Display different calculated measure values for different end of the month dates.

[Anonymous]

Hi All,

Can you please help me with the below scenario.

For current month end date: (29 Feb 2025)

(sum of sales where Date1 <= today()) - (sum of the sales where Date2 <= end of month today) + (sum of sales where Date2 >= end of month of today() && Date1 >= today() && Date1 <= end of month today)

For next month end date: (31 Mar 2025)

(sum of sales where Date1 <= today()) - (sum of the sales where Date2 <= end of month March) + (sum of sales where Date2 >= end of month of March && Date1 >= today() && Date1 <= end of month March)

and so on ... for every month end different calculation. It should display these values against each month end in a list or line graph.

Thanks in advance, 

Shiva.

[bhanu_gautam]

@Anonymous Create a Date Table (if you don't already have one):

DateTable = CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31))

Create Measures:

DAX
SumOfSales_Date1 = CALCULATE(SUM(Sales[Amount]), Sales[Date1] <= TODAY())

SumOfSales_Date2_EndOfMonth =
VAR EndOfMonthDate = EOMONTH(TODAY(), 0)
RETURN
CALCULATE(SUM(Sales[Amount]), Sales[Date2] <= EndOfMonthDate)

SumOfSales_Date2_Conditions =
VAR EndOfMonthDate = EOMONTH(TODAY(), 0)
RETURN
CALCULATE(
SUM(Sales[Amount]),
Sales[Date2] >= EndOfMonthDate &&
Sales[Date1] >= TODAY() &&
Sales[Date1] <= EndOfMonthDate
)

DAX
DynamicMeasure =
VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)
VAR NextMonthEnd = EOMONTH(TODAY(), 1)
RETURN
SWITCH(
TRUE(),
SELECTEDVALUE(DateTable[Date]) = CurrentMonthEnd,
[SumOfSales_Date1] - [SumOfSales_Date2_EndOfMonth] + [SumOfSales_Date2_Conditions],
SELECTEDVALUE(DateTable[Date]) = NextMonthEnd,
[SumOfSales_Date1] - CALCULATE(SUM(Sales[Amount]), Sales[Date2] <= NextMonthEnd) +
CALCULATE(
SUM(Sales[Amount]),
Sales[Date2] >= NextMonthEnd &&
Sales[Date1] >= TODAY() &&
Sales[Date1] <= NextMonthEnd
),
-- Add more conditions for other months if needed
BLANK()
)

Use a line chart or a table to display the DynamicMeasure against the DateTable[Date].

[Anonymous]

Hi Bhanu, 

Thank you for your reply. But still I'm not getting the required output.

For eg, I have taken your formula and created below measure for just one simple calculation. 

DynamicMeasure =
VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)
VAR NextMonthEnd = EOMONTH(TODAY(), 1)
RETURN
SWITCH(
TRUE(),
SELECTEDVALUE(Backlog_2[OPD]) = CurrentMonthEnd,
CALCULATE(SUM(Backlog_2[Sales]), Backlog_2[Est. Ship Date] <= EOMONTH(TODAY(),0)
))

Result:

Here, the DynamicMeasure is giving 713983.87 as it is filtering for the month end 28 feb. But I need the entire calculation "[SumOfSales_Date2_EndOfMonth]" which is 13276175.28 to be stored in 28th feb 2025. 

Can you please help me.

Thanks,

Shiva.

[Anonymous]

Hi @Anonymous , hello bhanu_gautam, thank you for your prompt reply!

From your description, it seems that the original post is different from the latest post. Could you please help verify the actual requirements?