Forum Discussion
Cumulative Total and Filter
Hi Community,
this is my first post and I have already browsed the web for solutions, but somehow, I am stuck. 😕
So I am trying to show cumulated training completions per date (run-up) in a graph - see below:
When I use the filters on the left, the charts look exactly as I planned to.
But if I remove the filter and show the "overall cumulations", then the graphs are screwed. 😞
I am using the following formula to calculate the cumulative completions.
Cumulative MEASURE =
VAR LastVisibleDate =
MAX ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] )
VAR FirstVisibleDate =
MIN ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] )
VAR LastDateWithSales =
CALCULATE (
MAX ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] ),
REMOVEFILTERS ('TKLMSREPORT - 2021-08-02T144129 529'[Completion Date])
)
VAR Result =
IF (
FirstVisibleDate <= LastDateWithSales,
CALCULATE (
COUNTA([User ID]),
'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] <= LastVisibleDate
)
)
RETURN
Result
VAR LastVisibleDate =
MAX ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] )
VAR FirstVisibleDate =
MIN ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] )
VAR LastDateWithSales =
CALCULATE (
MAX ( 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] ),
REMOVEFILTERS ('TKLMSREPORT - 2021-08-02T144129 529'[Completion Date])
)
VAR Result =
IF (
FirstVisibleDate <= LastDateWithSales,
CALCULATE (
COUNTA([User ID]),
'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] <= LastVisibleDate
)
)
RETURN
Result
What's wrong? I also found a way to make the overall look good, but then the filter per region does not work:
Cumulative MEASURE =
CALCULATE(COUNTA('TKLMSREPORT - 2021-08-02T144129 529'[User ID]),
FILTER (ALLEXCEPT('TKLMSREPORT - 2021-08-02T144129 529', Tabelle1[Title]),
'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] <= MAX ('TKLMSREPORT - 2021-08-02T144129 529'[Completion Date])
)
)
CALCULATE(COUNTA('TKLMSREPORT - 2021-08-02T144129 529'[User ID]),
FILTER (ALLEXCEPT('TKLMSREPORT - 2021-08-02T144129 529', Tabelle1[Title]),
'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] <= MAX ('TKLMSREPORT - 2021-08-02T144129 529'[Completion Date])
)
)
2 Replies
VAR Result = IF ( FirstVisibleDate <= LastDateWithSales, CALCULATE ( COUNTA([User ID]), 'TKLMSREPORT - 2021-08-02T144129 529'[Completion Date] <= LastVisibleDate ) )What should happen if the condition is not met?
