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
- jtownsend217 years ago
Responsive Resident
The item showing "last 24 months" in my graph would have the number you are asking for.
Do you mean that you need it to show the labels?
- Anonymous7 years agoNot applicable
Yes am expecting that one.