Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Tricky - Sum Formula Based on the Month Column in Table

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 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

1 Reply

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!