Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Total not reading correctly

Hello Friends, 

 

Below I have calculated the rolling 12 M sales. The logic on this rolling 12M is basically as below. 

If I'm looking for the rolling 12 month for Jan 2022, it should take the sales from Jan 2021 - Dec 2021. 

Below is the DAX for that. 

 

Sales LY_R12 =
var prevmo=LASTDATE(PREVIOUSMONTH('Date'[Day]))
var result=
CALCULATE([Sales],DATESINPERIOD('Date'[Day],prevmo,-12,MONTH))
return
result
 
Below is a the screenshot of the table. In here my total I wanted to read is the latest month R12, instead I'm getting the very first month of the data set. Any suggestions would be very helpful. 
 

 

KR,

Sandeep 

Need Help

11 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Can you change it so it returns prevmo? I think that will give us a clue as to what is happening.

     

    Also what is the last date in your date table? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The above table I have to update to BI service which is going to take a bit of time to fetch the updated values from DW. But for quick glance I have made the change in the desktop version as you requested. Here the timeline of data is from 201901-202112. As you can see the total and the first month of the data series are matching (even though its blank). the prevmo is returning exactly what I want 

       

       

       

      • bcdobbs's avatar
        bcdobbs
        Community Champion
        4 years ago

        Just mocking up a demo. Will get back to you!