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

Question

How to construct DAX measure to calculate the sum of YTD value for a 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 the 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 updates.

We would like to have the 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 – the correct value of 66 in the yellow Card. However, this solution is not automatic. I want to see the 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 the 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 too harsh. It is applied to the fact table first, selecting only one month, and then calculating YTD value wrongly. So if there would be an option for forcing order of calculation and filtering, there would be hope.
2. I tried SWITCH function to display the proper results for a 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.

Answer 1

• 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])

•  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]) ) )

If you are preparing for the Power BI certification exam, then take up this Power BI online training by Intellipaat that offers instructor-led training, hands-on projects, and certification.