Forum Discussion

jonfrank3366's avatar
jonfrank3366
New Member
4 years ago
Solved

Week over week line graph comparing Q1 vs Q2 data?

Hello,

 

I am trying to create a line graph that compares weekly data in Q1 vs Q2. I have created a custom column for Week of Quarter and created separate measures to compare each quarter. My current line graph is getting the correct total figures but is ignoring the time series context and just giving me a flat line. Image below. 

 

 

Here is an example of one of the measures I'm using:

 

Q1 DemGen Total =
CALCULATE([DemGenTotal],
DATESINPERIOD(
Date_Lookup[Date],
DATE(2022,01,01), 1,QUARTER))

 

I believe the measure context is overruling the context from the x-axis. I think I need to make some edits to the above measure but I'm not sure what I'd need to change.

 

Anybody able to lend some insight?

 

Thanks!

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi jonfrank3366 ,

    According to your description, I create a sample and it can get the correct result by your formula.

    I guess it may be a matter of week of quarter or [DemGen Total] measure.

    I attach my sample below, you can see what's different from yours.

     

    Best Regards,
    Community Support Team _ kalyj

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    jonfrank3366 , Just create a measure for this Qtr vs last qtr

     

    Last Qtr =
    var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _max = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))
    var _min = eomonth(_min,-3)+1
    return
    CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))
    //Or use all
    CALCULATE([net] ,Filter(all('Date'[Date]),'Date'[Date]>= _min && 'Date'[Date] <= _max))

     

     

    Qtr =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1
    return
    CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))

     

    or trailing qtr for last qtr

    trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi jonfrank3366 ,

    According to your description, I create a sample and it can get the correct result by your formula.

    I guess it may be a matter of week of quarter or [DemGen Total] measure.

    I attach my sample below, you can see what's different from yours.

     

    Best Regards,
    Community Support Team _ kalyj

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