Forum Discussion
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
- v-yueyunzh-msftCommunity Support
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