Forum Discussion
Cumulative for Cumulative Result using DAX
Hello All,
I'm using the following DAX to calculate previous year Sales Cumulative.
Prev Yr Sales **bleep** = CALCULATE(SUM(FactTable[Sales]),FILTER(ALL(DateTable),DateTable[FYear]=MAX(DateTable[FYear])-1&&DateTable[Fin Month]<=12)))
For
Cur Year **bleep**=CALCULATE(SUM(FactTable[Sales]),FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]<=MAX(DateTable[Fin Month])))
**bleep** for Cur Yr **bleep** =
var a= CALCULATE([Cur Year **bleep**],FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]=MAX(DateTable[Fin Month])))
var b= CALCULATE([Cur Year **bleep**],FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]=MAX(DateTable[Fin Month])-1))
var c= CALCULATE([Cur Year **bleep**],FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]=MAX(DateTable[Fin Month])-2))
var d= CALCULATE([Cur Year **bleep**],FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]=MAX(DateTable[Fin Month])-3))
var e= CALCULATE([Cur Year **bleep**],FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]=MAX(DateTable[Fin Month])-4))
var f= CALCULATE([Cur Year **bleep**],FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]=MAX(DateTable[Fin Month])-5))
var g= CALCULATE([Cur Year **bleep**],FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]=MAX(DateTable[Fin Month])-6))
var h=CALCULATE(SUM(FactTable[Sales]),FILTER(ALL(DateTable),DateTable[FYear]=MAX(DateTable[FYear])-1&&DateTable[Fin Month]<=12)))
return
a+b+c+d+e+f+g+h
"**bleep** for Cur Yr **bleep**" is the measure which I want to display in report,Consider I selected FinYear as "2021-2022" and Month is September, In Variables the calculation should happen till var f, but in my scenario var g is navigating Prev Yr Last Month Value. How to avoid this??
var g= CALCULATE(SUM(SUM(FactTable[Sales]),FILTER(ALL(DateTable),DateTable[Fin Year]=SELECTEDVALUE(DateTable[Fin Year])&&DateTable[Fin Month]=MAX(DateTable[Fin Month])-6)) - with this condition for the above period selection it's showing blank
I'm attaching the snap for Data and my expected output.Help me out on this.
I have my Fact Table and Date table Dimension table .In Fact Table I have Time period in "YYYYMM" format ,in DateTable ' it's in DD-MM-YYYY also. So as Many-Many Relationship arises to avoid that I created Calendar Bridge Table which is DISTINCT(DateTable[YYYYMM])
Relationship will be like
FactTable[Month]-Calendar[YYYYMM]
Calendar[YYYYMM]-DateTable[YYYYMM](Bidirectional Filtering)
All the above calculations I created are Measures only
TIA
1 Reply
- amitchandak
Super User
likhithar , Create a date from Year Month and and then use time intelligence using date table
Date = Date(left([Month year],4), right([Month Year],2), 1)
or
Date = eomonth(Date(left([Month year],4), right([Month Year],2), 1),0)
You can use measures like
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))