QTD Total regardless of Slicer values

[Anonymous]

Hi everyone,

So I have account amounts that get loaded at the end of every month, and on my report I have a slicer at the top where I can select the Load date to look at that snapshot in time essentially, but I also want to see the QTD total of the amounts.

Amount	AccountNumber	DateLoadDate
$        5.00	A34445	1/31/2020
$        8.00	B63748	1/31/2020
$     10.00	H64859	1/31/2020
$        4.00	K99833	1/31/2020
$     10.00	A34445	2/29/2020
$        6.00	B63748	2/29/2020
$        7.00	H64859	2/29/2020
$        2.00	K99833	2/29/2020
$        8.00	A34445	3/31/2020
$        6.00	B63748	3/31/2020
$        4.00	H64859	3/31/2020
$        4.00	K99833	3/31/2020

So in a perfect world when I click the 1/31 load date I'd only get $27, when I select 2/29 I'd get $52, when I select 3/31 I'd get the sum of the entire quarter, so $74. And then the next quarter it starts all over again. I also have my table linked to a TimePeriod calendar table, and I've marked it as a date table in PowerBI.

I've tried doing things like this

CALCULATE(TOTALQTD(sum(Amount),Dim_TimePeriod[DT]))

But when I select 3/31 it only shows the amounts for 3/31, not any of the unselected periods that are also in the quarter. I've tried doing things with DATESQTD and ALL(Dim_TimePeriod) and I'm still only getting values for the selected period. I'm also unsure if this needs to be a measure or calculated column?

Any ideas?

Thanks!

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

@Greg_Deckler 

Thanks for the quick reply, I downloaded your pbix and I do think this is what I want but it doesn't seem to be working for me when I use the same logic in my file

This is what you had for the Time Intelligence measure, I changed it to QTD instead of YTD and in your workspace it looks to be working exactly how I'd want it to.

Measure = TOTALQTD(SUM('TimeIntelligence'[Value]),'Calendar'[Date])

But when I bring it over I'm only getting values for the selected period? I can't seem to attach a file unfortunately but if you look at my original table and I select 3/31 I'm getting 22 in my Measure. I should be getting 74.

Thanks!

[Anonymous]

@Greg_Deckler 

Tried using TheHardWay calculations instead and still same issue

VAR __MaxYear = MAX(Dim_TimePeriod[YR])

VAR __MaxQ = MAX(Dim_TimePeriod[QTR])

VAR __TmpTable = CALCULATETABLE(TABLE,ALL(Dim_TimePeriod[YR]),all(Dim_TimePeriod[QTR]))

RETURN SUMX(FILTER(__TmpTable, [PeriodDate].[Year]=__MaxYear && [PeriodDate].[QuarterNo] = __MaxQ),[Amount])

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

1. Create a Dates Table. Create relationship between your fact table.

Dates =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Year-Month", FORMAT ( [Date], "YYYY-MMM" ),
    "Quarter", QUARTER ( [Date] )
)

2. Create another Calendar table, without any relationship among other tables.

Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Year Month", FORMAT ( [Date], "YYYY-MMM" ),
    "Quarter", QUARTER ( [Date] )
)

3. Create a Measure like so:

Measure = 
VAR SelectedYearMonth =
    SELECTEDVALUE ( 'Calendar'[Year Month] )
VAR SelectedYear =
    CALCULATE (
        MAX ( 'Calendar'[Year] ),
        'Calendar'[Year Month] = SelectedYearMonth
    )
VAR SelectedMonth =
    CALCULATE (
        MAX ( 'Calendar'[Month] ),
        'Calendar'[Year Month] = SelectedYearMonth
    )
VAR SelectedQuarter =
    CALCULATE (
        MAX ( 'Calendar'[Quarter] ),
        'Calendar'[Year Month] = SelectedYearMonth
    )
VAR QTD_ =
    IF (
        SelectedYearMonth = BLANK (),
        TOTALQTD ( SUM ( 'Table'[Amount] ), Dates[Date]),
        IF (
            HASONEVALUE ( Dates[Year-Month] ),
            IF (
                MAX ( Dates[Year] ) = SelectedYear
                    && MAX ( Dates[Quarter] ) = SelectedQuarter
                    && MAX ( Dates[Month] ) <= SelectedMonth,
                TOTALQTD ( SUM ( 'Table'[Amount] ), Dates[Date] )
            ),
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                FILTER (
                    Dates,
                    Dates[Year] = SelectedYear
                        && Dates[Quarter] = SelectedQuarter
                        && Dates[Month] <= SelectedMonth
                )
            )
        )
    )
RETURN
    QTD_

4. Create a slicer using "Year Month" column from Calendar table.

5. Test.

For more details, please check the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.