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 the years) to automatically populate with information for the current period.

 

I have tried creating a measure based on the information I have to work out if todays date is in the date column then it should equal that period as per below then I'm hoping to add a column to populate any with todays period to automatically extract it in the table but can't seem to find the initial calculation.

 

I know "Sum" and "isn't right and "date" doesn't work with the date column but I'm having trouble finding the right function and can't seem to find online where someone has asked the same question

 

Todays Period = if (TODAY() = sum('Tbl_Dates'[Date],sum('Tbl_Dates'[Pd-FY])))
 
Just to give an idea of what I'm trying to create:
 
Current Period2020/212021/222022/23
    
Need Help
  • amitchandak's avatar
    amitchandak
    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

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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