Tricky - Sum Formula Based on the Month Column in Table

[Anonymous]

Hi Expert

I want to dynamically diagonally sum the values as shown in the image below based on the sample data. Tricky here is the diagonal must stop if Period 1 or Month 1 goes outside the range. In the image below Feb 20 is the last point (which corresponds to the Sept 19 in the smaller table) And input the results into the table on the right hand side..

Image

Sample PBIX

https://www.dropbox.com/s/vj507mj3oie5c8m/dianglo.pbix?dl=0 

[v-kelly-msft · Accepted solution]

Hi  @Anonymous,

First create a calendar table as below:

calendar table = CALENDAR(MIN('Sample'[Month]),MAX('Sample'[Month]))

Then create a relationship between calendar table and fact table.

Finally create a measure as below:

Measure = 
VAR _1=CALCULATE(SUM('Sample'[1]),DATEADD('Sample'[Month],5,MONTH))
VAR _2=CALCULATE(SUM('Sample'[2]),DATEADD('Sample'[Month],4,MONTH))
VAR _3=CALCULATE(SUM('Sample'[3]),DATEADD('Sample'[Month],3,MONTH))
VAR _4=CALCULATE(SUM('Sample'[4]),DATEADD('Sample'[Month],2,MONTH))
VAR _5=CALCULATE(SUM('Sample'[5]),DATEADD('Sample'[Month],1,MONTH))
VAR _6=CALCULATE(SUM('Sample'[6]),FILTER(ALL('Sample'),'Sample'[Month]=MAX('Sample'[Month])))
Return
_1+_2+_3+_4+_5+_6

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!