Forum Discussion
Helper IIPayroll Calendar and Pay Period Numbers
Hi, PowerBIFreak
Please correct me if I wrongly understood your question.
I think, you can easily create the calendar table like below for the first four columns.
In order to create the last column in the below picture, please try the below.
Period Number =VAR currentdate = 'Calendar'[Date]RETURN"Period "& ROUND (DIVIDE (SUMX (FILTER ( 'Calendar', 'Calendar'[Date] <= currentdate ),IF ( 'Calendar'[Day of Week] = 1, 1 )),2),0)Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Super UserPowerBIFreak , in such case you have to populate the year start and year end date on all records . Same way period start and end dates and then you deal these things with help of Rank
New columns in date table
Period Rank = RANKX(all('Period'),'Period'[period start date],,ASC,Dense)
Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)
Measures
This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))
Same way week deal with weeks
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
PowerBIFreakThis is way beyond my level! But I still want to try it. On my calendar table, I have only one column, the date column from 12/21/20 to 12/19/21. So, on the Period and Year rank function, after the "all", it should be the column name, am I correct?
Thanks.
Best way to do that, I only had to do some slight tweaking for my use.
I already had start and end dates setup and used the ranking to setup a filter for the last 9 periods
Nice and very useful