Date Hierarchy vs Actual Dates - measure not working

[SuryaDave]

Hello Experts,

My question may be very very simple.

Basically,  I want to create a measure that will calculate previous months values  & in the table visual i want to show month ending date, current month value and previous month values.  There are 2 conditions that though that I have to comply with ..

1. Use the date column coming from data only 

2. Not create any addition calendar table or any other date / period table 

The problem I am facing is, my previous month value measure is doing correct calculation when I use it with date hierarchy but DO NOT WORK when I use actual dates.  I need to use actual dates in my requirements.  I will need to create table visual and chart visual for this current month and prev month comparison.

Hope any of you can guide me further & also teach me to fish by educating me on where I am going wrong. 

Thanks

Thankshttps://nswhealth-my.sharepoint.com/:u:/g/personal/surya_dave_health_nsw_gov_au/EWayEah1BzhMpG02bgZA... 

[SuryaDave]

@maruthisp 

Thanks a ton - it surely solves my problem. I will accept this as answer.

If you don't mind just spending 2 more minutes and explain - why my previous measure was not working and what is happening under the hood with the solution that you suggested ? That will help me understand how dax is actually woring internaly

[maruthisp · Accepted solution]

Hi SuryaDave,

Please find the explanation for the DAX expressions:

Previous Month Value =
VAR CurrentDate = MAX('Sheet1'[Data_Dt])
VAR PreviousMonthDate = EOMONTH(CurrentDate, -1)
RETURN
CALCULATE(
SUM('Sheet1'[Values]),
FILTER(
ALL('Sheet1'),
'Sheet1'[Data_Dt] = PreviousMonthDate
)
)

Finds the latest date in the current filter context (e.g., the current row in a table or the selected month in a slicer). Calculates the last day of the previous month using EOMONTH(CurrentDate, -1).
Then filters the entire table to find rows where the date is exactly equal to that previous month-end date.
Sums the values for that specific date.

PrevMonth_Value =
CALCULATE(
SUM(Sheet1[Values]),
PREVIOUSMONTH(Sheet1[Data_Dt].[Date])
)

Uses the built-in PREVIOUSMONTH() function to get the entire previous month based on the current filter context. Then sums the values for all dates in that previous month.

Please let me know if you have any questions further. Thanks.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[SuryaDave]

@maruthisp  - Awesome ! Thanks for detailed response - explanation - more power to you !

[maruthisp]

Hi SuryaDave,

Please find the DAX expression as per your shared Power BI File:

Previous Month Value =

VAR CurrentDate = MAX('Sheet1'[Data_Dt])

VAR PreviousMonthDate = EOMONTH(CurrentDate, -1)

RETURN

CALCULATE(

    SUM('Sheet1'[Values]),

    FILTER(

        ALL('Sheet1'),

        'Sheet1'[Data_Dt] = PreviousMonthDate

    )

)

Please let me know if you have any questions further. Thanks.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X