Compare values from end of month M with value start of month M+1

DAX
1

Hello

can someone help me with the following problem
I want a dax formula to compare the value of last day of the month M with the first day of the month M+1
I tried the new index() and window() but didn’t succeed
kind regards and thanks in advance
Roger

image
image758×219 6.98 KB

CHECK FOR PREVIOUS STOCK NUMBER .xlsx (16.8 KB)

2

Hi @Roger ,

You can run your DAX query directly on this site: Data Mentor Queries. Simply type your DAX question into the query field provided.

Best regards,

Enterprise DNA Support Team

3

Hi @Roger,

You may check Data Mentor Tools Advisor created thread created at:

Hope it helps.

4

Ensure you have a date table in your model and it is related to your date column in your source table. You can add one column to that table if you don’t have it already to capture the start of the month:

StartOfCurrentMonth = EOMONTH(Dates[Date],-1)+1

and then you can calculate 3 measures:

Measures 
FirstDayOfCurrentMonthValue = 
CALCULATE(
    [Sum KG],
    FILTER(
        SOURCE,
        SOURCE[DATE] = RELATED(Dates[StartOfCurrentMonth])
    )
)

LastDayOfPreviousMonthValue = 
CALCULATE(
    [Sum KG],
    FILTER(
        ALL(Dates),
        Dates[Date] = EOMONTH(MAX(Dates[Date]), -1)
    )
)

Difference * 1000 = 
IF(
    ISBLANK([LastDayOfPreviousMonthValue]) || ISBLANK([FirstDayOfCurrentMonthValue]),
    BLANK(),
    [LastDayOfPreviousMonthValue] - [FirstDayOfCurrentMonthValue]
)*1000

Then you could recreate your visual as:

image

5

Thank you very much for the solution,

Roger