Forum Discussion

likhithar's avatar
likhithar
Icon for Helper III rankHelper III
4 years ago

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

  • 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])))