YTD change in percentage based on a non standard calendar

[PowerBIFreak]

Hi folks,

I need your help with this issue. I saw a video on youtube using the PARALLELPERIOD to compare the time period change in percentage. However, my calendar table is not a standard table. I have a calendar table that started on lets say december 21, 2020 and ended in maybe the beggining of 2022. That is our payroll calendar. Then, I have created a column to calculate the pay period number. Basically, the payperiod number is every 14 days. So, on the pay period number column, I have number 1 to 26.

Then, on the fact table I have posting date, category (Normal Time, Over time, vacation...) and Hours. So, I was able to get the normal time of each pay period because I have a relationship with the calendar table. I can filter out and calculate the "Normal Time" hours for each period. Now, how do I do the YTD change of each period? Can anyone help?

Thank you.

[v-yangliu-msft · Accepted solution]

Hi  @PowerBIFreak ,

I created some data:

Calendar Table

Hours Table:

Here are the steps you can follow：

1. Create calculated column.

Pay_Period = RELATED('Calendar Table'[Pay Period])

Result:

2. Create measure.

Total =
SUM('Hours Table'[Hours])

Category Percentage =
var _total=SUMX(FILTER(ALL('Hours Table'),'Hours Table'[Pay_Period]=MAX('Hours Table'[Pay_Period])),[Hours])
var _1=SUM('Hours Table'[Hours])
return
DIVIDE(_1,_total)

Measure =
var _1=CALCULATE([Category Percentage],FILTER(ALL('Hours Table'),[Category]=MAX('Hours Table'[Category])&&[Pay_Period]=1))
var _2=CALCULATE([Category Percentage],FILTER(ALL('Hours Table'),[Category]=MAX('Hours Table'[Category])&&[Pay_Period]=2))
return
_2-_1

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBIFreak]

Hello,

Thanks for your help. This is exactly what I was trying to do. Now, my question to you is when you said you created some data, you mean you created some measures, correct? I didn't see any difference on the data I provided. 

Again, thanks for your help.

[v-yangliu-msft]

Hi  @PowerBIFreak  ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

It is not very clear of your expected results. What does each period in YTD change of each period refer to...

Can it be expressed in the form of a chart or in more detail, so as to better help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBIFreak]

Hi Yang,

I was trying to attach the file but I don't think could. But, I have a print screen here below. I hope you understand what I want to achieve in here. For example, the "Normal Time" for Pay Period 1 and 2. When we compare these two, there should be a -57%, right? I want to create another column so I can show the trend for each category. Also, the percentage calculation is let say for Normal Time, that would be Normal Time/Sum(Normal Time, Over Time, Sick Time, Vacation)

I'm a rookie to Power Bi and I learned a lot from this forum. I appreciate your help and others as well.

Thanks again!

[amitchandak]

@PowerBIFreak , for this you need have few things in a date table

Year start date- Based on your FY

new columns

Day of Year =datediff([Year Start date] , [Date],Day) +1

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)

Assume you have month/period no and qtr or qtr no

measures

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Qtr] <= Max('Date'[Qtr]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Qtr] <= Max('Date'[Qtr])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[PowerBIFreak]

Thank you amitchandak. I really appreciate your time helping me on this issue. I have tried the code but didn't work.  Regardless, thank you!  🙂

[PowerBIFreak]

I don't have the qtr number on my table. Do I need it? If I do, I will add it. Thanks so much.