Need to calculate sum based on ALL dates up to and including Month selected from slicer

Roseventura — Mon, 14 Nov 2022 17:23:17 GMT

Hi,

I have a slicer for the Month name. When a user selects the month from the slicer, I need to calculate Backlog for not only that month, but all backlog <= the last date of the month selected unless you select current month so it would be MTD in that case.

Example: In the example below, November is selected. So I need the table to display ALL backup up to and including the current date in November (11/14/22). Currently it is showing me backlog ONLY for November (all years).

If a user chooses October, the last fiscal day of the month is 10/29. I need the user to see ALL backup up to and including 10/29/22. In this picture, the table is displaying ONLY October Backlog (all years). I don't want it to go beyond 10/29/2022 and I need to see everything UP TO 10/29/22.

NOTE: I use a 445 custom date calendar so DAX date functions DO NOT work for me. My month name is directly tied to the FISCAL month, so if a user selects August from the slicer the fiscal dates in August are 7/31/2022 to 8/27/2022.

I'm using this measure to get the last fiscal day in the month selected:

Last Fiscal DiM =

VAR SelYear = SELECTEDVALUE(Dates[PADFYR], year(today()) )

VAR SelMonth = SELECTEDVALUE(Dates[PADFMO], month(today()) )

VAR GetLastDiM = calculate(Maxx(Dates, [paddate]), filter(Dates, Dates[padfyr] <= SelYear && Dates[PADFMO] <= Selmonth) )

return

if( isblank(GetLastDiM) || SelMonth = month(today()), today(), GetLastDiM )

I'm using this measure to calculate all allocated backlog for dates up to an including the selected date:

Total Backlog Allocated to Date =

CALCULATE(

[Total Backlog Allocated $$] ,

all(Dates ),

filter(Backlog, Backlog[Customer Request Date] <= [Last Fiscal DiM]) )

In plain English I need the above measure to give me all backlog where the Customer Request Date is <= the Last Fiscal DiM selected (example November 14, 2022). I thought using ALL(DATES) would cause my Allocated measure to ignore all dates and just use the value of the date that results from the Last Fiscal DiM measure, but that is not working. (Is it because the Last Fiscal DiM measure DOESN'T ignmore the Date filtering?)

Any help would be greatly appreciated!

Re: Need to calculate sum based on ALL dates up to and including Month selected from slicer

Greg_Deckler — Mon, 14 Nov 2022 18:15:10 GMT

Roseventura You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

This may also help: (3) Better Year to Date Total - Microsoft Power BI Community

Re: Need to calculate sum based on ALL dates up to and including Month selected from slicer

Roseventura — Mon, 21 Nov 2022 13:49:09 GMT

Your articles, while interesting did NOT solve my problem. This seems to be a simple problem with DAX, not with my Date calendar.

topic Re: Need to calculate sum based on ALL dates up to and including Month selected from slicer in DAX Commands and Tips

Need to calculate sum based on ALL dates up to and including Month selected from slicer

Re: Need to calculate sum based on ALL dates up to and including Month selected from slicer

Re: Need to calculate sum based on ALL dates up to and including Month selected from slicer