Forum Discussion
Quarter to date calculation using month slicer as a reference point
Good evening
I am not new to power bi but just can't seem to find a way to do this. I have a simple dashboard set up (dummy data extracts and pbix attached). There are two visuals controlled by one month slicer, each visual looking at a different year. My issue is that I need to be able to report on a qtr to date basis in both years, but using the slicer as a reference point. eg) if searching for month 6, then the figures should relate to that whole qtr, so months 4, 5 and 6. If searching for month 5, then only months 4 and 5 should be included.
I have tried using a running total, additional columns to group data together, as well as All, All Selected and All Except. In essence I get to a point where all the figures by month are correct but cannot create the next step to reference the slicer and add month totals together where needed. Any help would be appreciated, it feels like I've spent far too long on this already.
The two tables are linked single direction from date table.
| Date | Year | Qtr | Mth | Value |
| 01/01/2022 | 2022 | 1 | 1 | 49535 |
| 02/01/2022 | 2022 | 1 | 1 | 8045 |
| 03/01/2022 | 2022 | 1 | 1 | 8857 |
| 04/01/2022 | 2022 | 1 | 1 | 34820 |
| 05/01/2022 | 2022 | 1 | 1 | 9433 |
| 06/01/2022 | 2022 | 1 | 1 | 47808 |
| 07/01/2022 | 2022 | 1 | 1 | 7870 |
| 08/01/2022 | 2022 | 1 | 1 | 29341 |
| 09/01/2022 | 2022 | 1 | 1 | 38373 |
| 10/01/2022 | 2022 | 1 | 1 | 38977 |
| Date | Mth | Year Diff |
| 01/01/2022 | 1 | 1 |
| 02/01/2022 | 1 | 1 |
| 03/01/2022 | 1 | 1 |
| 04/01/2022 | 1 | 1 |
| 05/01/2022 | 1 | 1 |
| 06/01/2022 | 1 | 1 |
| 07/01/2022 | 1 | 1 |
| 08/01/2022 | 1 | 1 |
| 09/01/2022 | 1 | 1 |
| 10/01/2022 | 1 | 1 |
| 11/01/2022 | 1 | 1 |
| 12/01/2022 | 1 | 1 |
| 13/01/2022 | 1 | 1 |
2 Replies
- Greg_Deckler
Community Champion
CraigM4 Not sure I completely understand but perhaps something like the following:
Measure = VAR __Month = MAX('Actual'[Mth]) VAR __Quarter = MAX('Actual'[Qtr]) VAR __Table = FILTER(ALL('Actual'), [Qtr] = __Quarter && [Mth] <= __Month) VAR __Result = SUMX( __Table, [Value] ) RETURN __ResultThanks Greg, I'll give that a try and let you know.
