Forum Discussion

parry2k's avatar
parry2k
Icon for Super User rankSuper User
10 years ago

How to dynamically update MTD/QTD/YTD for SSAS MD

    Hello all,   So far this is very helpful community and able to overcome some of the challenges I have using SSAS MD and PowerBI.   Now here is interesting one and I'm sure it is easy one to...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

I think you can achieve what you are after by never using the MDX NOW() function.

 

Just code your calculated measures using the [Dates].[Calendar].currentMember as you have done and get them right for manually picking the date.

 

Then in your date dimension, add a new column called [Days from today] and make it a DATEDIFF(DAY,datekey,GETDATE()).

 

Then in your Power BI you can select the [Days from today] filter in your report page and set it to = 0 (or 1 for yesterday)

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    10 years ago

    Hello Jolly,

     

    I'm glad you proposed this solution which I already tried. When I put the [Date From Today] on the filter, it doesn't automatically shows MTD/QTD/YTD.

     

    Just to test, I added date slicer in PowerBI and filter on [Days From Today] and I see date slicer is showing correct date based on the [Days From Today] but MTD/QTD/YTD will only get updated when I actually select date in the slicer, not sure if I'm missing something here.

     

    Thanks,

    P

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      What kind of visual are you using for your MTD? Are you trying to display a single value?  or are you trying to display this as a trend over time?

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        10 years ago

        I'm just showing it as a card. Thanks!