Difference previous month of rolling distinct values in measures by month

[Anonymous]

Hi Community,

need a bit of help.

I have distint values each month and a cumulative total of all distinct values.

How can I get the difference each month?
i.e. I would have Apr = 272 or empty , May = 76 (348-272), June 68 (416-348)

Slicer is set for dates, so the difference should start where the dates are set.

All I´ve tried (and I think that is all what the forum provides) does not result into any usable result.

Esp. all the previousmonth or dateadd or var with "-1" result in the cumulative rolling distinct and not showing the above expected distinct value increase.

All values are Measures and not available in any column.

Thanks a lot!

[v-jingzhang]

Hi @Anonymous 

What is the smallest granularity of your data, Date or Month? Do you use a Date table? It would be helpful if you could provide some sample data of the table and codes of the measures.

Usually when using Time Intelligence functions like previousmonth or dateadd, we need to have a Date table in the model and create a relationship between Date table and Fact table on Date columns. Then create measures like below,

Sales Amt = SUM(FactOnlineSales[SalesAmount])

Sales Amt PM = CALCULATE([Sales Amt],DATEADD(DimDate[Datekey],-1,MONTH))

Sales Amt Diff PM = [Sales Amt] - [Sales Amt PM]

See articles:

Period comparisons in Power BI | by Nikola Ilic | Towards Data Science

Month over Month Calculation in Power BI using DAX - RADACAD

Month-over-Month calculation using DAX in Power BI – SQLServerCentral

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Hi @v-jingzhang , 

thanks for your response, maybe I need to specify a bit more.

I´m working with a separate date table, with relationship to all different sources, smallest value is date.

All above values are measured values, not coming from a calculated or dax-created table.

All values are also able to filter via date slicer.

Single distinct value is a measure.
Calculate, Distinct Count + Filter

Cumulated distinct count is a measure.

Calculate, [single distinct value], Filter(allselected(table), table(date) <= max(table(date))

I have tried everything incl. your mentioned function, but I dont get the difference between one cumulated/rolling distinct month and the previous.

Thanks & Best regards

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Thanks for this info. You could try below code to get the cumulated distinct value for previous month. Then use [Cumulated distinct count] - [cumulate distinct count previous month] to get the difference between months. 

cumulate distinct count previous month =
VAR previousMonthEnd = EOMONTH ( MAX ( 'calendar'[Date] ), -1 )
RETURN
    CALCULATE (
        [single distinct value],
        FILTER (
            ALLSELECTED ( 'calendar'[Date] ),
            'calendar'[Date] <= previousMonthEnd
        )
    )

Let me know if you have any questions.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

You saved my day! Thanks a lot!