Measure in DAX to calculate YTD for chosen month only for Power BI

Question

How to construct DAX measure to calculate sum of YTD value for specific month?

Here we have FactTable grouped by months. FactTable is filled with both Actual data and Forecast data. The only way to know when Actual end is information in table [Cut of date] in column [End of YTD]. In table [Cut of date] in column [End of YTD] – it is a single value table – we have the interesting chosen month, for which we want to see the calculation of YTD. In our case it is March. FactTable is updated irregularly every month with usually one month delay. There is no way of linking it to time functions like TODAY because of irregular update.

We would like to have a correct value of YTD displayed in yellow Card Visual for the month [End of YTD]. When we click on the slicer on "2018-03" we get almost what we want – correct value of 66 in the yellow Card. However this solution is not automatic. I want to see correct value automatically when the [End of YTD] month changes, in our case to April or then to May. I do not want it done by user.

My desperate effort can be downloaded from file: DAX YTD.pbix

I pursued the deer in various ways:

1. By using FILTER function in DAX measures. But it seems that the FILTER function is to harsh. It is applied to fact table first, selecting only one month, and then calculating YTD value wrongly. So if there would be any option for forcing order of calculation and filtering, there would be hope.
2. I tried SWITCH function to display proper result for specific month and 0 or null for other months. Although I succeed in this, I was not able to take advantage of it. When it came to filtering I was as hopeless as before. BTW I would be able to make it if SWITCH produced totals at the end of the table, but it does not. Surprisingly.
3. I put some hopes in RELATED function to display proper results in the [Cut off date] table. I have not walk out of the fog so far.

I would appreciate your help.

Update before bounty. Going to higher level. I have introduced a Category column to FactTable. Please download DAX YTD by category.pbix. So filtering gets more complex now. I would like to have correct YTD figures for Apples category.

Solution 1

Did you use the Date column from the Calendar table, instead of the one from FactTable?

If you use the date column from FactTable, when you apply a filter on the date, it will filter on the fact records which is in March, and then do the calculation afterwards, hence the result 33.

If you use the one from Calendar, when you apply a filter on it, it filters the records on Calendar (which you want to show in the chart), so the underlying calculation will still remain intact.

---

A working example:

Calendar = CALENDAR(DATE(2010, 1, 1), DATE(2020, 12, 31))

I suggest you to change the calculations of the measures to avoid missing values in some cases:

Total = SUM(FactTable[Value])
MTD = TOTALMTD([Total], 'Calendar'[Date])
YTD = TOTALYTD([Total], 'Calendar'[Date])

---

UPDATE:

It's much clearer to me what you want to achieve now but it still seems an XY problem to me.

I understand that you want to show the dashboard as is so that users do not need to click/input every time to see what they are supposed to see. That's why I don't get why you need to create a new table to store the Cut off date (End of YTD). How is it going to be maintained automatically?

The relative date filtering solution above actually still works in the .pbix file you've shared. If you drag the Date column from the Calendar table to visual level filters for the yellow card and add the relative date filtering, it should work as below:

For the End of YTD visual, you can use the following measure to get the first day of last calendar month, so you don't need to create another table for it:

End of YTD = EOMONTH(TODAY(), -2) + 1

And hopefully this is what you want to achieve:

Updated file for your reference.

---

UPDATE again:

I think you'll have to write your own YTD calculation instead of using the built-in one, so that you can make use of the cut off date you defined in another table. Here I assume that you have one and only one row in 'Cut off date'[End of YTD]. Note that I've added ALL() to the filter, so that the yellow card remains the same (66) instead of showing blank when some other rows/filters are clicked:

YTD_Special = 
CALCULATE(
    [Total],
    FILTER(
        ALL(FactTable),
        FactTable[Date] >= DATE(YEAR(VALUES('Cut off date'[End of YTD])), 1, 1) &&
        FactTable[Date] <= VALUES('Cut off date'[End of YTD])
    )
)

Solution 2

I would resolve this by adding a calculated column to your Calendar table to categorise each row into either "YTD" or "Other", e.g.

Is YTD =
IF (
    [Date] >= DATE ( YEAR ( DISTINCT ( 'Cut off date'[End of YTD] ) ), 1, 1 )
        && [Date] <= DISTINCT ( 'Cut off date'[End of YTD] ),
    "YTD",
    "Other"
)

I would then add the new Is YTD field to the Visual level filters of your Card visual, and choose YTD from the Basic filtering list. The measure shown can be your simple Total measure: SUM(FactTable[Value]).

This is a far more flexible and resuable solution than any specific measure gymnastics. You will not need an explosion of measures to apply the required logic on top of every base measure - they will all just work naturally. You can apply the filter at any level: Visual, Page, Report, or put it in a Slicer for control by the end user.

I prefer to return text results e.g. "YTD" / "Other" (rather than 1/0, True/False or Yes/No), as this allows for easy extension to other requirements e.g. "Prior YTD" (1 Jan 2017 to 1 Mar 2017). It also is clearer when used in visuals.

Actually I shouldn't claim the credit for this design - this roughly follows how Cognos Transformer's Relative Time functionality worked back in the 90s.

Comments

[Przemyslaw Remin]

Do you also filter by Calendar.Date? I have twelve months for 2018, so I cannot use relative filtering as you did, because I would end up in December. I have another, third table which has only value for CurrentMonth=2018-02-01

[Foxan Ng]

What do you want to achieve actually? I thought you've given precise requirement already but I'm confused now. It doesn't matter how many dates you have in the Calendar table for the relative date filtering. As you can see, my Calendar table contains all the dates from 2010 to 2020, but the relative date filtering is with reference to Now (2018-04-09 or 2018-04-10 depends on your timezone), not any dates in the table.

[Przemyslaw Remin]

Sorry for being not specific at the first time. I have greatly revised my question. I would be very grateful for any small tip. Thank you for taking time so far.

[Przemyslaw Remin]

The report has to be done for queen bee. The queen bee will not click on any slicers. The FactTable is filled with Actual data and Forcast data. Another working bee (not me) will load new Actual data when appropriate time comes. You need Cut off date (End of YTD) to be able to know when Actual ends and Forcast begins. The yellow visual is meant for Actual only. There is no connection between Cut off date (End of YTD) and real calendar. The working bee may load new Actual data 10 days after end of calendar month or 25 days. So I must get the filter from table.

[Foxan Ng]

@PrzemyslawRemin I understand your frustration. Perhaps it's easier to change the approach so that it'll work for this special case..Please check the update above.

[Przemyslaw Remin]

It works in the simple example, hurray! In my real case it requires further playing on filters, some including, some excluding. No easy way. Thanks a lot.

[Foxan Ng]

@PrzemyslawRemin If you want the YTD_special to be updated by other filters, you can remove the ALL() function to see if it works for you. Glad to help!

[Przemyslaw Remin]

Can you please explain what is the logic in your formula? Why not simply compare dates Calendar.Date >='Cut off date'[End of YTD] ? Why do you use Distinct?

[Mike Honey]

It's a calculated column, not a measure. So a naked reference to a column from another table is not valid syntax. DISTINCT is a handy shortcut function to grab a single value, when you have a single-row table like your 'Cut off date'.

[Przemyslaw Remin]

How to modify your dax measure so that Cut off date'[End of YTD] is changed to EOMONTH? Cut off date'[End of YTD] is stored as the first day of month ie 2018-03-01 although it means March 31.

[Przemyslaw Remin]

If you would like to grab the single value not with distinct but with a MAX or other function, how then your dax measure should be modified? Suppose we have different YTD for categories.

[Mike Honey]

For end-of-month, just wrap the field in an ENDOFMONTH function, e.g. Is YTD = IF ( [Date] >= DATE( YEAR( DISTINCT( 'Cut off date'[End of YTD] ) ) , 1 , 1 ) && [Date] <= DISTINCT( ENDOFMONTH( 'Cut off date'[End of YTD] ) ) , "YTD" , "Other" )

[Mike Honey]

Note it's a calculated column, not a measure.

[Mike Honey]

For your MAX scenario, just replace DISTINCT with LASTDATE.

[Mike Honey]

For your "different YTD for categories" scenario, you would need a Calendar table with a row for each date and each category, and a relationship to a Categories table. That's do-able but a bit more complex than described in your question.