Forum Discussion

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

How to show MTD data in a visual

Hello all,

 

Just trying to work on MTD and need some help. I have a location table and continuous calendar table and both linked on datekey. The problem we are trying to solve here is to see MTD count depends on date selected in a "date slicer", if no date selected then use current date as to calculate MTD.

 

I added a following measure to calculate MTD and it works great.

 

MTD New = TOTALMTD(COUNT(DIM_Locations[LocationKey]), 'Calendar'[Date])

Here is the sample result in the table:

 

 

 

I also add a column in calendar table to identify current day selected and it looks something like this:

 

IsThisCurrentDay = if(ISFILTERED('Calendar'[Date]), DAY('Calendar'[Date])=DAY(VALUES('Calendar'[Date])), DAY('Calendar'[Date])=DAY(TODAY()))

 

I added a graph and filtered this where IsThisCurrentDay = TRUE, looks great as of March 13, we see data for each month as MTD upto thirteen of each month

 

 

 

Now the challenge is when I select the date in date slicer, I want each month to show data upto selected day of the date in the slicer, for example if I select "March 10th, 2017" in slicer, I should get MTD for upto 10th for each month but my graph doesn't show any value as soon as I select a date in the slicer. I'm sure my formula for isThisCurrentDay is not working and not sure what is the best solution.

 

 

 

Thanks in advance for help!

 

Parv

Need Help

11 Replies

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

    Small change I did to MTD formula (added bold) but still no success:

     

    MTD New = TOTALMTD(COUNT(DIM_Locations[LocationKey]), 'Calendar'[Date], ALL('Calendar'))
    • Reid_Havens's avatar
      Reid_Havens
      Icon for Most Valuable Professional rankMost Valuable Professional
      9 years ago

      Hi parry2k,

       

      If I'm understanding your scenario correctly you'd like to use a slicer to filter the values to show MTD with a range of (1st - x value) in all months correct? One way to potentiall accomplish this: create a Day of Month slicer in the calendar table, turn on multi-select, and then select the month day's you'd like to see in the table. Screenshot example of this below. The slicer shown in your image should filter to just that day in a single month. This slicer would filter ALL months to the date range.

       

       

      Calculated Column (Day of Month):

      =DAY( DateTable[Date])
      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        9 years ago

        Thanks for the suggestion but we want user to select a date and get MTD, instead of multiple selection, that is not true MTD because use can miss to select a day. Also I was hoping to more elegant solution using DAX etc.