MTD Till Previous Day

DAX DAX Calculations
1

Hi Team,

I am in need of a quick help here. I am trying to calculate MTD till previous day using dax in my power pivot data model (for example I am sitting on 19th Sep 2021 and i need to figure out MTD till 18th Sep 2021) but all I get is last day (previous day) sale. Can you please help me in this regards? My dax is as follows:

MTD Till Previous Day = CALCULATE ([MTD Volume],DATEADD(SalesData[Date],-1,DAY))

[MTD Volume] is my measure for MTD sale, “SalesData” is my fact table.

Kindly do the needful.

Regards
Harish Rathore

2

@harishrathore ,

The reason your DAX isn’t working is that you’ve only defined the upper bound of the MTD calculation. You also need to filter on the lower bound (the start of the month) of your selected date. I’ve created a simple example using the eDNA Practice Dataset External tool:

MTD Prev Date = 
VAR CurrentDate = [Harvest Date]
VAR CurrentLessOneDay = CurrentDate - 1
VAR BeginMonth = 
DATE( YEAR( CurrentDate), MONTH(CurrentDate), 1 )

VAR Result = 
CALCULATE(
    [Total Sales],
    Dates[Date] >= BeginMonth &&
    Dates[Date] <= CurrentLessOneDay
)

RETURN Result

image

This example uses a disconnected table slicer configured to “after” to only select a single date with the slider.

I hope this is helpful. Full solution file attached.

2 Likes
3

Thanks a lot @BrianJ for the solution. This is what I needed. Thanks Mate.

Regards
Harish Rathore

1 Like