cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kofo Helper I
Helper I

QTD Total regardless of Slicer values

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 AccountNumberDateLoadDate
 $        5.00A344451/31/2020
 $        8.00B637481/31/2020
 $     10.00H648591/31/2020
 $        4.00K998331/31/2020
 $     10.00A344452/29/2020
 $        6.00B637482/29/2020
 $        7.00H648592/29/2020
 $        2.00K998332/29/2020
 $        8.00A344453/31/2020
 $        6.00B637483/31/2020
 $        4.00H648593/31/2020
 $        4.00K998333/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!




1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: QTD Total regardless of Slicer values

Hi @Kofo ,

 

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] )
)

qtd1.PNG

 

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.

qtd2.PNG

 

5. Test.

qtd.gif

 

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.

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: QTD Total regardless of Slicer values

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...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Kofo Helper I
Helper I

Re: QTD Total regardless of Slicer values

@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!
Kofo Helper I
Helper I

Re: QTD Total regardless of Slicer values

@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])
Community Support
Community Support

Re: QTD Total regardless of Slicer values

Hi @Kofo ,

 

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] )
)

qtd1.PNG

 

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.

qtd2.PNG

 

5. Test.

qtd.gif

 

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.

View solution in original post

Community Support
Community Support

Re: QTD Total regardless of Slicer values

Hi @Kofo ,

 

Is this problem solved?

 

 

Best Regards,

Icey

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors