Forum Discussion

jonathanhowell's avatar
jonathanhowell
New Member
3 years ago
Solved

Show Data from a Specific Date

Hi all,

 

I am hoping this is an simple question and is just something I'm overlooking.

I want to create a bar graph that shows comparisons between the current quarter's data and that of last quarter, last year, etc. That part is done.

I also want to compare against data before the COVID-19 pandemic started (Q2 2019 thru Q1 2020). To try to do this, I created a calculated column thus:

 

PrePandemicDate = IF( RIGHT(DateTable[FiscalQtr],2) == "Q1", 
DATE(FORMAT("2020","yyyy"), FORMAT(DateTable[Date],"mm"), FORMAT(DateTable[Date],"dd")), 
DATE(FORMAT("2019","yyyy"), FORMAT(DateTable[Date],"mm"), FORMAT(DateTable[Date],"dd")))

 

 

and used a grouping table to gather the bars together:

 

Axis Value = 
SWITCH(
    SELECTEDVALUE('Axis Labels'[Axis]),
    "Current Qtr",DIVIDE(SUM('Reports'[Value]), SUM('Reports'[DenomValue])),
    "Prev Qtr",CALCULATE(DIVIDE(SUM('Reports'[Value]), SUM('Reports'[DenomValue])), PREVIOUSQUARTER(DateTable[Date])),
    "This Qtr Prev Year",CALCULATE([Current Qtr], SAMEPERIODLASTYEAR(DateTable[Date])),
    "Pre-Pandemic",CALCULATE([Current Qtr],FILTER(DateTable,DateTable[PrePandemicDate])),
    "High Clinic",MAXX(	KEEPFILTERS(VALUES('Reports'[ClinicID])),CALCULATE(DIVIDE(SUM('Reports'[Value]), SUM('Reports'[DenomValue])))),
    "Low Clinic",MINX(KEEPFILTERS(VALUES('Reports'[ClinicID])),CALCULATE([Current Qtr]))

 

 

to create the pretty graphs:

However, I am seeing the same result between "Current Quarter" and "Pre-Pandemic" and I cannot figure out why. It has something to do with context, I'm sure. Can someone lend me some help?

 

Thank you in advance,

Jonathan

 

  • Hi , jonathanhowell 

    For your dax , i see you use the calculated colimn as the filter.The calculated column return the whole table , so you don't calcuate the " (Q2 2019 thru Q1 2020)." just the whole context . 

    If you want to calculate the " (Q2 2019 thru Q1 2020)." you can try to use this filter in CALCULATE() function:

    FILTER(ALLSELECTED('DateTable'),  'DateTable'[Date] in which range date  )

     

    Best Regards,

    Aniya Zhang

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

1 Reply

  • Hi , jonathanhowell 

    For your dax , i see you use the calculated colimn as the filter.The calculated column return the whole table , so you don't calcuate the " (Q2 2019 thru Q1 2020)." just the whole context . 

    If you want to calculate the " (Q2 2019 thru Q1 2020)." you can try to use this filter in CALCULATE() function:

    FILTER(ALLSELECTED('DateTable'),  'DateTable'[Date] in which range date  )

     

    Best Regards,

    Aniya Zhang

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