cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Measure to make a monthly cumulative distinct count by grouping variable

Hello,
I want to perform a cumulative distinct count over time using a grouping variable.

My data is

IdStatusDateFlagPYearMonth
1Apples1/10/201801
1Bananas1/20/201811
1Grapes3/18/201823
2Bananas2/18/201812
2Apples4/10/201824
2Grapes5/10/201835
3Grapes1/10/201811
3Bananas3/12/201823


I want to display the Year+month in the x-axis, the status in the legend and the status of the Id in the end of the month as Y axis.
The chart I want to obtain is:
Untitled.png

I have tried a few measures but so far I haven't been able to obtain my desired result.
I beleive my closest metric is the one below using an external calendar, however it does fail in the months without measurement:

Measure 3 = 
VAR myDate =  CALCULATE(MAX('Calendar'[YearMonth]),ALLEXCEPT('Calendar','Calendar'[YearMonth]))
var myTab = SELECTCOLUMNS (
            (FILTER(SUMMARIZE('Table1','Calendar'[YearMonth],Table1[Id],Table1[Flag],Table1[Status],"col",CALCULATE(max(Table1[Flag]),ALLEXCEPT('Table1',Table1[Id]),'Table1'[PYearMonth]<=myDate)), [col]=Table1[Flag])),
        "aux",Table1[Id]
        )
RETURN(
COUNTROWS(myTab)
    )

Any help is appreciated.

 
2 REPLIES 2
Highlighted
Resident Rockstar
Resident Rockstar

Re: Measure to make a monthly cumulative distinct count by grouping variable

Hi @JonArgandona ,

It seems that you want to create the Stacked column chart in Power BI.

I have a little confused about your value field in the Stacked column chart.

If it is convenient, could you explain the logic of your Y -axis in more details so that we could help further on it?

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Measure to make a monthly cumulative distinct count by grouping variable

Hello @v-piga-msft ,

As an initial point, the Status variable marks the state changes for each Id over time.

In my Y-axis I want to reflect the number od Ids in a state when the month ends.

For instance,

At month 1 (1/31/2019):
Id 1 has Status Bananas (last obs. 1/20/2018)
Id 2 has no records available

Id 3 has Status Grapes (last obs. 1/10/2018)

 

At month 2 (2/28/2019):

Id 1 has Status Bananas (last obs. 1/20/2018)
Id 2 has Status Bananas (last obs. 2/18/2018)

Id 3 has Status Grapes (last obs. 1/10/2018)

 

At month 3 (3/31/2019):

Id 1 has Status Grapes (last obs. 3/18/2018)
Id 2 has Status Bananas (last obs. 2/18/2018)

Id 3 has Status Bananas (last obs. 3/12/2018)


and so on ...

 

Let me know if you need any further clarification.

Thanks,

Jon

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors