Forum Discussion

loafers's avatar
loafers
Frequent Visitor
3 years ago

Time Intelligence for specific years

Hi,

 

Currently I have time intelligence measures set up as follows:

 

Current:

 
Spoiler
Current Year Revenue =
    VAR _TimeIntelligence=SELECTEDVALUE(TimeIntelligence[Time])
    VAR _Revenue = SWITCH(_TimeIntelligence,
                                "MTD",CALCULATE([Revenue],DATESMTD(DATEADD('Calendar'[Date],0,MONTH))),
                                "QTD",CALCULATE([Revenue],DATESQTD(DATEADD('Calendar'[Date],0,QUARTER))),
                                "YTD",CALCULATE([Revenue],DATESYTD(DATEADD('Calendar'[Date],0,YEAR))))

return _Revenue
 
Prior:
 
Spoiler
2 Year Back Revenue =
    VAR _TimeIntelligence=SELECTEDVALUE(TimeIntelligence[Time])
    VAR _Revenue = SWITCH(_TimeIntelligence,
                                "MTD",CALCULATE([Revenue],DATESMTD(DATEADD('Calendar'[Date],-2,YEAR))),
                                "QTD",CALCULATE([Revenue],DATESQTD(DATEADD('Calendar'[Date],-2,YEAR))),
                                "YTD",CALCULATE([Revenue],DATESYTD(DATEADD('Calendar'[Date],-2,YEAR))))

return _Revenue
 
There is a slicer for MTD/QTD/YTD and also a slicer for the Month. 
 
If I wanted to have the measures just show a year, so just 2021, 2020, etc, how can I adjust the measure for that? So needs to be conpatible with MTD/QTD/YTD but just for that specific year. My date table will go to 12/31 of the current year (so in this case 12/31/2023). My stakeholders want to have the measures show just 2020, 2021, 2022, etc so that they don't get confused on the year when exporting the data. I could adjust the Number of Intervals in DATEADD annually but this is a report that I'm handing off soon so it's easier to just have them create a measure for the year annually and drag/drop into the visual versus going through all the time intelligence measures to adjust for the period when it's a new year. 
Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    loafers , In such case we use independent table and show more years then selected

     

    You have to change this logic based on the selected value

     

    for three years

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = eomonth(_max, -36) +1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))