Forum Discussion

eeidris's avatar
eeidris
Regular Visitor
4 years ago
Solved

Create a graph for year end and current year

Hi everyone,

 

I want to create this graph in Power BI where I have every Dec numbers only for the past 3 years, and for current year I will have data from Jan to (Max Month in Current Year).

 

Currently I have a date table and the value for current year will change based on the date slicer that I have i.e. if I choose Sep-21, the graph will show Dec-18, Dec-19, Dec-20, Jan-21 to Sep-21 something like this:

 

 

 

This is my formula now but it can only show Dec data and current month. I dont know how to pass any date measure to show current year data as well.

 

EOY Graph =
VAR CurrentDate = MAX('DateTable'[Reporting Date])
VAR PreviousYear1 = DATE(YEAR(SELECTEDVALUE('Date'[Reporting Date]))-1,12,31)
VAR PreviousYear2 = DATE(YEAR(SELECTEDVALUE('Date'[Reporting Date]))-2,12,31)
VAR PreviousYear3 = DATE(YEAR(SELECTEDVALUE('Date'[Reporting Date]))-3,12,31)
Var Result =
SUMX('Table 1',IF(('Table 1'[Reporting Date] in {PreviousYear1,PreviousYear2,PreviousYear3,CurrentDate}), CALCULATE([Measure1])))
RETURN
Result

 

I have look around but I cant find the answer that I want. If you have seen this solved before OR you know how to do this, pls let me know.

 

Thanks so much for your help!

Ee Idris

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    4 years ago

    eeidris , Such a dynamic grouping based on selection is difficult

     

    You can have static one line till last year display year and for this year display month

     

    New column = if(year([Date]) < year(today()) , format([Date],"YYYY") , format([Date],"MMM-YYYY") )

     

    sort on

     

    Sort Column  = if(year([Date]) < year(today()) , format([Date],"YYYY\0\0") , format([Date],"YYYYMM")  )

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    eeidris , Such a dynamic grouping based on selection is difficult

     

    You can have static one line till last year display year and for this year display month

     

    New column = if(year([Date]) < year(today()) , format([Date],"YYYY") , format([Date],"MMM-YYYY") )

     

    sort on

     

    Sort Column  = if(year([Date]) < year(today()) , format([Date],"YYYY\0\0") , format([Date],"YYYYMM")  )