Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jonathanhowell
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:

jonathanhowell_0-1667322151470.png

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

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
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

View solution in original post

1 REPLY 1
v-yueyunzh-msft
Community Support
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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.