Comparing perfomance previous years

[dustdaniel]

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

[vojtechsima]

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.

[vojtechsima · Accepted solution]

@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]

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.

[dustdaniel]

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]

@dustdaniel check out my latest post here.