Help with measures for monthly and cumulative including TREATAS()

[Petja]

Hi,

I need to create two measures: one shows DISTINCTCOUNT(table[ID]) for every month in a year and another a cumulative sum for the same DISTINCTCOUNT(table[ID]).

I need to use TREATAS to connect a column from table to calendar.

I think I can get the correct for monthly using this:

CALCULATE(

    DISTINCTCOUNTNOBLANK(Table[ID]),

    TREATAS(VALUES(Calendar[Date]), Table[Date]),

    DATESYTD(Calendar[Date]),

    )

But can't get the cumulative sum measure to work. It should also reset yearly.

[Elena_Kalina · Accepted solution]

Hi @Petja 

Monthly Distinct IDs =

CALCULATE(

    DISTINCTCOUNTNOBLANK('Table'[ID]),

    TREATAS(VALUES('DimDate'[Date]), 'Table'[Date])

)

Cumulative Distinct IDs YTD =

VAR CurrentDate = MAX('DimDate'[Date])

VAR CurrentYear = YEAR(CurrentDate)

VAR YearDates =

    FILTER(

        ALL('DimDate'[Date]),

        YEAR('DimDate'[Date]) = CurrentYear &&

        'DimDate'[Date] <= CurrentDate

    )

RETURN

CALCULATE(

    DISTINCTCOUNTNOBLANK('Table'[ID]),

    TREATAS(YearDates, 'Table'[Date])

)

[Petja]

Hey, thank you for all the replies, haven't had time to test all of them. I ended up getting coherent results with using USERELATIONSHIP() instead of TREATAS() but there was a working solution here using TREATAS().

[v-prasare]

Hi @Petja,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @Petja,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@Ashish_Mathur, @Elena_Kalina, @cengizhanarslan & @krishnakanth240, thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Ashish_Mathur · Accepted solution]

Hi,

Does this work?

CALCULATE(DISTINCTCOUNTNOBLANK(Table[ID]),userelationship(Table[Date],Calendar[Date]),DATESYTD(Calendar[Date]))

To the visual, drag date from the Calendar table.

If it does not work, then share the download link of the PBI file.

[cengizhanarslan]

1) Monthly DISTINCTCOUNT (per month only)

IDs (Monthly) =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( 'Table'[ID] ),
    TREATAS ( VALUES ( 'Calendar'[Date] ), 'Table'[Date] )
)

Put Calendar[Month] (or Year-Month) on the axis and this will return the distinct IDs for that month.

2) Cumulative DISTINCTCOUNT (YTD, resets each year)

IDs (Cumulative YTD) =
VAR DatesYTD =
    DATESYTD ( 'Calendar'[Date] )
RETURN
CALCULATE (
    DISTINCTCOUNTNOBLANK ( 'Table'[ID] ),
    TREATAS ( DatesYTD, 'Table'[Date] )
)

This evaluates the distinct IDs over the YTD date set, so it naturally resets in a new year (based on your Calendar).

[Elena_Kalina · Accepted solution]

Hi @Petja 

Monthly Distinct IDs =

CALCULATE(

    DISTINCTCOUNTNOBLANK('Table'[ID]),

    TREATAS(VALUES('DimDate'[Date]), 'Table'[Date])

)

Cumulative Distinct IDs YTD =

VAR CurrentDate = MAX('DimDate'[Date])

VAR CurrentYear = YEAR(CurrentDate)

VAR YearDates =

    FILTER(

        ALL('DimDate'[Date]),

        YEAR('DimDate'[Date]) = CurrentYear &&

        'DimDate'[Date] <= CurrentDate

    )

RETURN

CALCULATE(

    DISTINCTCOUNTNOBLANK('Table'[ID]),

    TREATAS(YearDates, 'Table'[Date])

)

[krishnakanth240]

Hi @Petja 

Can you try these measures. Happy to work if you can share the sample data of 'tables' with the 'columns'. Thank You!

Monthly IDs =
CALCULATE (
DISTINCTCOUNTNOBLANK('Table'[ID]),
TREATAS(VALUES(Calendar[Date]),'Table'[Date]))

Cumulative Monthly IDs =
VAR CurrDate = MAX(Calendar[Date])
RETURN
SUMX(VALUES(Calendar[MonthYear]),
CALCULATE([Monthly IDs],Calendar[Date]<= CurrDate))