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

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.