Return Total in Current Time Period (DAX)

[dalexand]

Greetings,

I expect there is an easy solution to my query that is currently evading me. What I am trying to do is calculate the sum of a quantity, say Sales, over the last 4 months. The catch is, I want the total quantity to be displayed on the current month on the pivot, leaving the previous months blank. So if January - April Sales are 10,000, I would want all 10,000 to show in April.
Any assistance is much appreciated.

 

[Matt Allington]

You can do pretty much anything, but it is not clear what the rules are. What happens if there are 5 months? Or 3 months? Etc. Are you just trying to write a YTD measure?

Here is some reading that may help. DAX Time Intelligence Explained - Excelerator BI

 

[dalexand]

Thanks Matt. Ideally, the time frame would be adjustable, so I could pull out the last 3 or 5 months. A simple YTD measure is a step in the right direction, but on a pivot with months, it would return a YTD for each month--- in my scenario I would return the total only in the current month, leaving the preceding months on the pivot table blank. Hope that makes sense.

 

[krrishkrsna]

take your data to PowerPivot window


add two columns


first column enter formula =FORMAT(Table1[Date],"mmm")
second column enter formula =IF(MONTH(Table1[Date])=4,Table1[Sales],0)


click on PivotTable icon > place first column in Rows area > second column in Values area