cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Showing 2 tables filtered for different dates (quarters) based on same field

Hi,

 

I am trying to show 2 tables, one showing a given quarter data (10s of columns) as per a slicer selection, the other table (same data) the next quarter. Quarter is decided by a date field which is the same field in each case. The only way I could do it so far is to have 2 different slicers (and associate them independently to the different table) which is not ideal for the user to say the least...

 

I do use a calendar table connected to my dates to keep everything clean (I don't think it matters but just to be thorough).

 

I tried to think about measures, hidding a filter that would update based on the other one... but I cannot get to a solution.

 

Thanks,

David

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Showing 2 tables filtered for different dates (quarters) based on same field

Hi @davidsa ,

 

Please try like this:

This Quarter =
VAR Q =
    SELECTEDVALUE ( 'Date'[Qtr] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Qtr] = Q && 'Table'[Year] = MAX ( 'Date'[Year] ) )
    )

Next Quarter =
VAR Q =
    SELECTEDVALUE ( 'Date'[Qtr] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Qtr] = Q + 1 && 'Table'[Year] = MAX ( 'Date'[Year] ) )
    )

3.PNG

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Showing 2 tables filtered for different dates (quarters) based on same field

Thanks Xue Ding.

I don't think it will work though.

It's not about the sales value, it is about the individual deals. 

 

I need to be able to filter on Q2 with a slicer and have one table that shows only Q2 deals (bold) and another table that shows only Q3 deals (Italic). I do not want to sum up, I want to show individual lines... 

 

Sample raw data:

DealCustomer ID Customer nameClose dateQuarterProductquantityValue
123ABCCustomer16/20/20Q2Product A1

100

124ABDCustomer26/15/20Q2Product B2200
125ABECustomer38/12/20Q3Product C3300
126ABFCustomer49/13/20Q3Product D4400
127ABGCustomer511/9/20Q4Product E5500
128ABHCustomer610/3/20Q4Product F6600

 

I don't think a measure is my way out but happy to be proven wrong...

 

Thanks,

David

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors