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 delivered from today for the next 6 month (Nov, Dec, Jan, Feb, Mar and Apr). And compare them with the previos years. 

 

I want to create a bar chart to compare the results per year and month

 

A matrix with the result would be something like this

 

Here is a file with Sample data link 

 

Thanks in advanced

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.

     

5 Replies

  • 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.