Customers from previous yr, current revenue

[Powerbiuserag]

If customer existed in sep23 then only I want revenue for sep24, if customer existed in oct23 then only I want it's rev from oct24.. my cirrent dax is giving me revenue for oct'24 correctly for Oct'23 customers, but it's showing same value for all the prior months.. ie not showing  revenue of sep'24 for customers that existed in sep,23..   

YTD_ClBal (SteadyState, BlockedCusts) =

VAR SelectedDate = MAX(zzzDateTable[Date])

VAR PreviousYearDate = EDATE(SelectedDate, -12)

VAR CurrentMonthStart = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1)

VAR CurrentMonthEnd = EOMONTH(SelectedDate, 0)

VAR CustomersPreviousYear =

    CALCULATETABLE(

        VALUES('LandExpandChurn'[Customer]),

        'LandExpandChurn'[DatePeriod] >= DATE(YEAR(PreviousYearDate), MONTH(PreviousYearDate), 1) &&

        'LandExpandChurn'[DatePeriod] <= EOMONTH(PreviousYearDate, 0),

        'LandExpandChurn'[Steady_State] = "SteadyState"

    )

RETURN

    CALCULATE(

        SUM('LandExpandChurn'[Revenue]),

        'LandExpandChurn'[Customer] IN CustomersPreviousYear,

        'LandExpandChurn'[DatePeriod] >= CurrentMonthStart &&

        'LandExpandChurn'[DatePeriod] <= CurrentMonthEnd

    )

[Anonymous]

Hi  @Powerbiuserag ,

Is your problem solved, if so, you can mark it as correct, if not, provide more detailed information and we can help you better!

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tharunkumarRTK]

@Powerbiuserag 

1. Did you mark your dateTable as Date Table? 

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables

This will remove filters on datetable if there are any. As it will add ALL(DateTable) by default.

2. In your calculate statement, you passed the filters on LandExpandChurn, assuming you created a relationship between date table and LandExpandChurn, you could pass filters on the date column in date table. 

3. I guess the below approach could give you the reulst that you are expecting, (I do not have your PBIX file, so I am justing in approach, this may or many work, depending the filter context that you have in your report, for further help please share the PBIX file).

YTD_ClBal (SteadyState, BlockedCusts) =


VAR CustomersPreviousYear =
    CALCULATETABLE(
        VALUES('LandExpandChurn'[Customer]),
        Dateadd(zzzDateTable[Date],-1,year)
    )

RETURN
    CALCULATE(
        SUM('LandExpandChurn'[Revenue]),
        CustomersPreviousYear
    )

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[rohit1991]

Hi @Powerbiuserag 

The issue lies in how your DAX query evaluates prior months relative to the Selected date. You are likely not filtering revenue separately for each month's customers from the previous year. Instead, it seems to reuse the customer filter for select date across all months, leading to the same revenue values being repeated.

Here’s how you can modify your measure to ensure it dynamically filters customers for each corresponding month of the prior year:

YTD_ClBal (SteadyState, BlockedCusts) =
VAR SelectedDate = MAX(zzzDateTable[Date])
VAR PreviousYearDate = EDATE(SelectedDate, -12)
VAR CurrentMonthStart = DATE(YEAR(SelectedDate), MONTH(SelectedDate), 1)
VAR CurrentMonthEnd = EOMONTH(SelectedDate, 0)

RETURN
CALCULATE(
SUM('LandExpandChurn'[Revenue]),
FILTER(
'LandExpandChurn',
'LandExpandChurn'[DatePeriod] >= CurrentMonthStart &&
'LandExpandChurn'[DatePeriod] <= CurrentMonthEnd &&
'LandExpandChurn'[Customer] IN
CALCULATETABLE(
VALUES('LandExpandChurn'[Customer]),
'LandExpandChurn'[DatePeriod] >= DATE(YEAR(PreviousYearDate), MONTH(PreviousYearDate), 1) &&
'LandExpandChurn'[DatePeriod]