Forum Discussion

YellowSquirrel's avatar
YellowSquirrel
Frequent Visitor
3 years ago
Solved

Custom Period Column Related to Today

Hi, My company work in financial periods over a custom 4 weeks. What I am looking to do is in a table that I am working on is for one of columns (where the other columns also show the breakdown of...
  • amitchandak's avatar
    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=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