Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to perform a cumulative distinct count over time using a grouping variable.
My data is
Id | Status | Date | Flag | PYearMonth |
1 | Apples | 1/10/2018 | 0 | 1 |
1 | Bananas | 1/20/2018 | 1 | 1 |
1 | Grapes | 3/18/2018 | 2 | 3 |
2 | Bananas | 2/18/2018 | 1 | 2 |
2 | Apples | 4/10/2018 | 2 | 4 |
2 | Grapes | 5/10/2018 | 3 | 5 |
3 | Grapes | 1/10/2018 | 1 | 1 |
3 | Bananas | 3/12/2018 | 2 | 3 |
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:
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.
Hi @Anonymous ,
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |