Drill down on cumulative ytd visual

DAX DAX Calculations
1

Hello - I am using the formula below from one of Sam’s videos. It works perfectly well when I have the Date on the x axis. It shows the cumulative orders received to date…and the rest of the future time periods are blank.

However, if I add Month to the visual, and I drill down, it shows all of the months…not just the ones with the values… and it shows those remaining months with the current ytd value.

Is there a way to modify this formula so that I can have the future months or weeks hidden? Just as it does when just looking at the Date level. Note “Date” in my visual is from the date table.

Cumulative Orders 2 =

VAR LastOrderDate = CALCULATE(LASTDATE(‘Orders 2’[Order Date]),ALL(‘Orders 2’))

RETURN

IF(SELECTEDVALUE(‘Date Table’[Date]) > LastOrderDate, BLANK(),

CALCULATE([Sum of Net Price 2],FILTER(ALLSELECTED(‘Date Table’),‘Date Table’[Date] <= MAX(‘Date Table’[Date]))))

2

Hello @richmont,

Thank You for posting your query onto the Forum.

You can use the “Extended Date Table Code” which is created by @Melissa and than use the “IsAfterToday” field and place it under the FILTER pane and only check the option of “FALSE”. Below is the screenshot provided for the reference -

image

By using it’ll prevent the future dates from taking into the account.

Also if you can provide the working file than the members of the forum can look into the formula part.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like