Forum Discussion

sonuojha1's avatar
sonuojha1
Helper IV
5 years ago

Monthwise Line graph with multiple measures for multiple selected year - dual axes

Hello All, I have a requirement to show line graph for all selected year from year slicer as new line on the graph for all months on x-axis. I have two measures called M1 and M2 and two y-axis as s...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

sonuojha1 , you need to have 4 measures. as legends not supported with multiple meausres.

 

You can use time intelligence

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

  • sonuojha1's avatar
    sonuojha1
    Helper IV
    5 years ago

    Thanks Amit for reply, 

    But none of the formula is giving correct result. one of the measures formula is as below:

    Result ( tried with your YTD formula- TimeSeriesEfficiency This Year)

     

     

    Actual calculation formula 

    Effici =
    VAR V_X=[Ther]*3412
    VAR V_Y=[Total Wa]*100
    var v_sum=IF(ISBLANK(V_X),0,V_X)+IF(ISBLANK(V_Y),0,V_Y)
    VAR V_RES=IFERROR(V_SUM/[Total G]/1000,0)
    RETURN
    IF(ISBLANK(V_RES) || V_RES=0,BLANK(),V_RES)