Forum Discussion
Custom Period Column Related to Today
- 3 years ago
YellowSquirrel , You have create a 444 or 445 calendar
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145sThen you need to have a period rank
based on period start date
Period Rank = RANKX(all(Period),Period[Period Start Date],,ASC,Dense)
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))This period today =
var _rank = maxx(filter('Date', Date[Date] =Today()) , Date[Period Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER((Period),Period[Period Rank]= _rank ))
Last period today =
var _rank = maxx(filter('Date', Date[Date] =Today()) , Date[Period Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER((Period),Period[Period Rank]= _rank -1 ))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
YellowSquirrel , You have create a 444 or 445 calendar
DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
Then you need to have a period rank
based on period start date
Period Rank = RANKX(all(Period),Period[Period Start Date],,ASC,Dense)
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))
This period today =
var _rank = maxx(filter('Date', Date[Date] =Today()) , Date[Period Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER((Period),Period[Period Rank]= _rank ))
Last period today =
var _rank = maxx(filter('Date', Date[Date] =Today()) , Date[Period Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER((Period),Period[Period Rank]= _rank -1 ))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Thanks for your help, this has been great