Forum Discussion

dustdaniel's avatar
dustdaniel
Helper II
1 year ago
Solved

Comparing perfomance previous years

Hi everyone, I tried searching in the community, I found simmilar post but I couldn't addapt it to my needs.   The question I'm looking to answer is How many services do I have pending to be delive...
Need Help
  • vojtechsima's avatar
    vojtechsima
    1 year ago

    dustdaniel 
    I created additional column in your calendar liek this:

     

     

    isValidDate = 
var currentDate = DateTable[Date]
var _year = DateTable[Calendar Year]
var month_start = MONTH(TODAY()) 
var day_start = DAY(TODAY())
var month_end = MONTH(MAXX(DATESINPERIOD(DateTable[Date], TODAY(), 6, MONTH), DateTable[Date]))
var day_end = DAY(MAXX(DATESINPERIOD(DateTable[Date], TODAY(), 6, MONTH), DateTable[Date]))
var check = IF( ( currentDate>= DATE( _year, month_start, day_start) && currentDate<= DATE(_year, 12,31) )  || ( currentDate >= DATE(_year, 1,1) && currentDate <= DATE(_year, month_end, day_end)), TRUE(), FALSE())

return   check

     

    What I do here is that I calculate today's window from now till 6 months, extract days and months of the start and end, and then for date I evaluate if the month a day is in that window with variable Year.


    And then measure like this:

     

     

    pending testing new = 
CALCULATE(
    [Total ReservasS],
    DateTable[isValidDate]
)

     

     


    I don't get the same results as you do, but I feel like this should be correct measure, calculation the same dates for each year.

     

vojtechsima's avatar
vojtechsima
Super User
1 year ago

Hello, dustdaniel ,
Here's how you can do it for one year:

pending = 
CALCULATE(
    [Total ReservasS],
    KEEPFILTERS(DATESINPERIOD(DateTable[Date], TODAY(), 6, MONTH))
)

I am trying to figure out how to do it for all years at once, but I am not sure that's possible in single measure.

  • dustdaniel's avatar
    dustdaniel
    Helper II
    1 year ago

    Thank you Voj,

     

    I think this is a good start, I tried something similar but I still need to compare with previous years.

    Since a year to year comparison would limit at some point with the last month of the period, maybe I could just create 2 visual, one from Today till end of year and another one from Jan till Apr?

     

    I was able to do this with a YTD custom columns 

     

  • vojtechsima's avatar
    vojtechsima
    Super User
    1 year ago

    dustdaniel 
    I created additional column in your calendar liek this:

     

     

    isValidDate = 
var currentDate = DateTable[Date]
var _year = DateTable[Calendar Year]
var month_start = MONTH(TODAY()) 
var day_start = DAY(TODAY())
var month_end = MONTH(MAXX(DATESINPERIOD(DateTable[Date], TODAY(), 6, MONTH), DateTable[Date]))
var day_end = DAY(MAXX(DATESINPERIOD(DateTable[Date], TODAY(), 6, MONTH), DateTable[Date]))
var check = IF( ( currentDate>= DATE( _year, month_start, day_start) && currentDate<= DATE(_year, 12,31) )  || ( currentDate >= DATE(_year, 1,1) && currentDate <= DATE(_year, month_end, day_end)), TRUE(), FALSE())

return   check

     

    What I do here is that I calculate today's window from now till 6 months, extract days and months of the start and end, and then for date I evaluate if the month a day is in that window with variable Year.


    And then measure like this:

     

     

    pending testing new = 
CALCULATE(
    [Total ReservasS],
    DateTable[isValidDate]
)

     

     


    I don't get the same results as you do, but I feel like this should be correct measure, calculation the same dates for each year.

     

    • dustdaniel's avatar
      dustdaniel
      Helper II
      1 year ago

      Thanks vojtechsima , 

       

      I just noticed that in order to get what I need, it's important to consider the creation date. We are going to compare the pending services from today till the next 6 month when these where requested before today, and I just did this excersice in different tables.

       

      I added the filter in each table manually so we can see the expected result but we need to compute it all together in one table 🙂

       

      I uploaded a new sample file Here . Thanks again in advance.