Dynamically Calculating Year-to-Date (YTD) Based on Selected Date Field in Power BI

[abishekvk]

I have two date fields: "Check Day" and "Work Day". I need a Year-to-Date (YTD) calculation that works dynamically for both fields. In Power BI, I can create metrics that work for individual date fields, but I'm trying to figure out a way to make the calculation dynamic for both date fields simultaneously.

Currently, I've managed to create separate calculations using TOTALYTD, like so:

TOTALYTD([Metric], 'MyDataSet'[Check Day].[Date])
TOTALYTD([Metric], 'MyDataSet'[Work Day].[Date])

However, I need to combine these into a single DAX calculation. When I select "Check Date" and YTD, it should calculate numbers based on the "Check Day" field. Similarly, when I select "Work Day", the calculation should be based on that date field.

How can I achieve this in DAX?

[abishekvk · Accepted solution]

Got this as a solution for the problem, getting almost the desired output.

VAR MaxDate = MAX(DataSet[W_Date])
VAR MaxDate2 = MAX(DataSet[C_Date])
VAR YTDValue =
SWITCH(TRUE,
ISINSCOPE( DataSet[W_Date]),
CALCULATE(
[Burden],
FILTER(
ALL(DataSet[W_Date]),
(DataSet[W_Date] <= MaxDate && YEAR(DataSet[W_Date]) = YEAR(MaxDate) )
)),
ISINSCOPE( DataSet[C_Date]),
CALCULATE(
[Burden],
FILTER(
ALL(DataSet[C_Date]),
(DataSet[C_Date] <= MaxDate2 && YEAR(DataSet[C_Date]) = YEAR(MaxDate2) )
)),
TRUE,
BLANK() -- Handles case where neither date field is selected
)

RETURN
YTDValue

[samratpbi]

You may try using Fields Parameter option. This is under Modeling -> New Parameter -> Fields. You can select which measures to choose. That will add a slicer in your report accordingly.

[abishekvk]

Doing this as part of creating a dataset for self service BI. We will be publishing just the dataset for the users and allowing them to create their own reports. So adding slicers or fields is not possible 

[MNedix]

Hi,

When you say date fields do you refer to columns in your dataset or individual measures?

[abishekvk]

Columns from my dataset. There is a cube where I have all these date fields and metrics