cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## 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?

Jonathan

1 ACCEPTED SOLUTION
Community 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

Community 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