Forum Discussion
Advocate VCumulative Count with Date Slicer
Hi All,
I have a Cumulative Count with a Date Slicer in a Table Visual. I have a date table which is linked to my FACT table by Date.
In the above screenshot , my first column is Cumulative Count.
If i change the date slicer the Cumulative Count becomes zero. I want the Cumulative Count to show based on the dates selected from the Date Slicer.
DAX for Cumulative Count = CALCULATE(
CALCULATE(COUNTROWS(DISTINCT(FACT_LogIn_Data[ID]));
FILTER(
ALLSELECTED('Date'[Date]);
ISONORAFTER('Date'[Date]; MAX('Date'[Date]); DESC)
)
) )
Not sure i should change the DAX for Cumulative measure or something. Please help me with this.
hi ncbshiva ,
Here's the test data I used:
Table “fact”
Table “date”
Please check following steps as below:
- Delete relationship if there is any relationship between table “date” and table “fact”.
- Create measure:
Measure =
CALCULATE (
COUNT ( 'fact'[id] ),
FILTER ( ALLSELECTED ( 'fact' ), 'fact'[date] <= MAX ( 'fact'[date] ) )
)
Measure 3 =
VAR sv =
SELECTEDVALUE ( 'date'[date], BLANK () )
RETURN
IF ( MAX ( 'fact'[date] ) > sv, BLANK (), [Measure] )
- Results would be shown as below:
Pbix as attached, hopefully works for you.
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
hi ncbshiva ,
Here's the test data I used:
Table “fact”
Table “date”
Please check following steps as below:
- Delete relationship if there is any relationship between table “date” and table “fact”.
- Create measure:
Measure =
CALCULATE (
COUNT ( 'fact'[id] ),
FILTER ( ALLSELECTED ( 'fact' ), 'fact'[date] <= MAX ( 'fact'[date] ) )
)
Measure 3 =
VAR sv =
SELECTEDVALUE ( 'date'[date], BLANK () )
RETURN
IF ( MAX ( 'fact'[date] ) > sv, BLANK (), [Measure] )
- Results would be shown as below:
Pbix as attached, hopefully works for you.
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear ncbshiva
Remove ALLSELECTED and try again.
For reference you can use the following link
http://tinylizard.com/all-vs-allselected/
Regards,
Munna
ncbshivaRemoving did not work
@
This solution might help you :
Requests Created Cumulative = CALCULATE( COUNT('Tablename'[RowNumber]), FILTER( 'Tablename', ('Tablename'[Created].[MonthNo] <= MAX ('CalendarTable'[Date]) ) ) )original post : https://community.powerbi.com/t5/Desktop/Cumulative-Count-by-Date/m-p/369480#M167429
May the "BI" force be with you!!!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly! Thanks!