How to show MTD data in a visual

[parry2k]

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

[parry2k]

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]

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]

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.

[Reid_Havens]

Have you ever created a disconnected slicer table? To avoid a multi-select scenario you would put a column from a Day of Month table on a slicer (similar to the slicer in the previous image I showed you), and then create another DAX measure called MAX Day that returns the maximum value from that table (31st by default).

Max Day = MAX( Table[DayofMonth] )

Then your IsCurrentDay formula can now reference that value like below. So when no single value is selected from the slicer it returns MTD from the entire month. But if a single day is selected, it should then return MTD up to that day in the month. See revised formula below.

IsCurrentDay=
IF (
    ISFILTERED ( Table[DayofMonth] ),
    DAY ( 'Calendar'[Date] ) = [Max Day],
    DAY ( 'Calendar'[Date] ) = DAY ( TODAY () )
)

[parry2k]

Thanks for the solution but I tried following and it seems to be working.

Created a measure in calendar table as below, Today is current date of the month (it is March 13th, 2017 as of now)

measureFlagCurrentDay = if(DAY(MAX('Calendar'[Date]))=DAY(MAX('Calendar'[Today])),1,0)

Add another measure in calendar table as below:

measure IsThisCurrentDay  = IF(ISFILTERED('Calendar'[Day]),1, [measureFlagCurrentDay])

Add a visual level filter on my visual as below

And this did the job, I still hoping that I can use date slicer (instead of DAY slicer) to achive the same.

[parry2k]

I also found caveat with this solution, if you select day 31 on the slicer, you will not see the months which doesn't have 31 days 😞 and that is why I'm hoping date slicer suppose to be better approach so that you can check if it is last date of the month then choose full months instead of number of days in a month, as i'm thinking more and more of it, it is getting little complicated.

Help!!!

Thanks,

P

[Reid_Havens]

So using the Disconnected Slicer table I was able to have months with less than 31 days still show up even after a slicer selection. 

The formula I used for the value calculation was:

=
TOTALMTD (
    [Total Profits],
    'Date Table'[Date],
    FILTER ( 'Date Table', DAY ( 'Date Table'[Date] ) <= [MAX Day] )
)

With the [Max Day] refrencing the Max value from my slicer table I made. I'm happy to send you my workbook with this as well. It's a dummy model I use for demo's.

[parry2k]

From  your example, you are showing data in a table with all dates are showing, if we select 10 from slicer, it should show only 10th of each month instead of 1..10th and that is where the issue will come if you use <= (less than) in your formula, you can test at your end.

Look at my original post with graph which shows current day from Today (march 13) for each month.

Thanks,

P

[Reid_Havens]

Showing the days on rows was only to show you the filtering being applied. If you remove that you'll only have the months on rows with the MTD up to the current selection. However if you're also wanting the day to show on rows as well that would add a bit of complexity to allow for that, instead of just showing the month, with a MTD slicer.

[parry2k]

Appreciate the effort but not showing the date will be very misleading for the users.

Based on the formula i posted earlier, everythign looks good except I need to extend it to find out if currently selected day is end of the month then include end of month date for each month, for example,. in case 31st is selected, then include every month that has last day 30th and ofcourse Feb as well. Here are some screen shots explaining how it is working now and need to fix last bit(could be very complex):

Scenario 1: no day selected on slicer (in this case it will show 13th of each month since current date is march 13th), this is working as expected

Scenario 2: 27th day selected on this is working as expected

Scenario 3: 31st day selected on this is NOT working as expected, just showing months which has 31 days

[Reid_Havens]

I would think if you could incorporate some sort of IF statement into the logic that might solve the scenario 3 problem. Where if the day from the slicer selection was ever greater than the ENDOFMONTH day in a given month, then return the value for the end of month day, otherwise return the day from your slicer selection.