Forum Discussion
Display total count in Power BI line chart
- 7 years ago
I think I got it figured out. My results won't match your data set because I had to recreate the sample data. It wouldn't let me copy and paste it from your doc because the size was too large. No worries though, I think I found a solution for you. First create a column as follows (you can add as many groups as you like, I only went to the last 24 months):
6 Month Increments = VAR _DateDiff = DATEDIFF(TODAY(),Sheet1[ModifiedDate],DAY) RETURN SWITCH( TRUE(), _DateDiff > (-180), "1. Last 6 Months", _DateDiff > (-360), "2. Last 12 Months", _DateDiff > (-540), "3. Last 18 Months", _DateDiff > (-720), "4. Last 24 Months", "5. Older" )Then create either use the quick measure creation tool I showed before, or just write one that mirrors the following:
Count of CommunityID running total in 6 Month Increments = CALCULATE( COUNTA('Sheet1'[CommunityID]), FILTER( ALLSELECTED('Sheet1'[6 Month Increments]), ISONORAFTER('Sheet1'[6 Month Increments], MAX('Sheet1'[6 Month Increments]), DESC) ) )This will give you the following result when you put them in your chart:
Here is a PBIX with the solution in it as well in case it helps.
I think I got it figured out. My results won't match your data set because I had to recreate the sample data. It wouldn't let me copy and paste it from your doc because the size was too large. No worries though, I think I found a solution for you. First create a column as follows (you can add as many groups as you like, I only went to the last 24 months):
6 Month Increments =
VAR _DateDiff =
DATEDIFF(TODAY(),Sheet1[ModifiedDate],DAY)
RETURN
SWITCH(
TRUE(),
_DateDiff > (-180), "1. Last 6 Months",
_DateDiff > (-360), "2. Last 12 Months",
_DateDiff > (-540), "3. Last 18 Months",
_DateDiff > (-720), "4. Last 24 Months",
"5. Older"
)Then create either use the quick measure creation tool I showed before, or just write one that mirrors the following:
Count of CommunityID running total in 6 Month Increments =
CALCULATE(
COUNTA('Sheet1'[CommunityID]),
FILTER(
ALLSELECTED('Sheet1'[6 Month Increments]),
ISONORAFTER('Sheet1'[6 Month Increments], MAX('Sheet1'[6 Month Increments]), DESC)
)
)This will give you the following result when you put them in your chart:
Here is a PBIX with the solution in it as well in case it helps.
Hi jtownsend21,
Thanks for your reply. We almost near to this i need one thing, is that possibile to show the total count as Full Inventory.
The Full Inventory Contains the total count (6+12+18+24 months)
Regards,
Yuvaraj