Forum Discussion

Sab-DFO's avatar
Sab-DFO
New Member
7 years ago
Solved

Display Fiscal Period FROM A MEASURE based on TODAY()

Hello,

 

I'm trying to display the fiscal period (P#) based on what today's date is. April is P1, May is P2, and so on until March (P12), and then a new fiscal year starts and the periods restart at P1. I created a measure using the DAX below, and the idea was that if I displayed this measure in a card visual, it would update the period automatically when we enter a new month. 

 

 

Unfortunately, when I display the measure in a card visual, it only displays (P12), even though as of today's date, it should be displaying (P2). Can anybody tell me what I am doing wrong ?

 

Thanks !

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Sab-DFO 

    In a date table, create such columns

    year = YEAR([Date])

month = MONTH([Date])

fiscal year = IF([month]>=4,CALCULATE(MAX('Table'[year]),FILTER(ALL('Table'),'Table'[year]=EARLIER('Table'[year]))),CALCULATE(MAX('Table'[year]),FILTER(ALL('Table'),'Table'[year]=EARLIER('Table'[year])-1)))

fiscal month = IF([month]>=4,[month]-3,[month]+9)

period = "P"&[fiscal month]

     

    Then create a measure

    today'period = CALCULATE(MAX('Table'[period]),FILTER(ALL('Table'),'Table'[Date]=TODAY()))

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Sab-DFO 

    In a date table, create such columns

    year = YEAR([Date])

month = MONTH([Date])

fiscal year = IF([month]>=4,CALCULATE(MAX('Table'[year]),FILTER(ALL('Table'),'Table'[year]=EARLIER('Table'[year]))),CALCULATE(MAX('Table'[year]),FILTER(ALL('Table'),'Table'[year]=EARLIER('Table'[year])-1)))

fiscal month = IF([month]>=4,[month]-3,[month]+9)

period = "P"&[fiscal month]

     

    Then create a measure

    today'period = CALCULATE(MAX('Table'[period]),FILTER(ALL('Table'),'Table'[Date]=TODAY()))

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    7 years ago

    You should create that logic in Power Query as a Period column in your Dates table, or as a calculated column if you created the date table in DAX.

     

    Then something along the lines of this will pull the relevant field. I pulled Month as I had that handy. You'd pull Dates[Period] though. The MAX() converts the one record table into a scalar value for your card.

    Test Measure = 
CALCULATE(
    MAX(Dates[Month]),
    Dates[Date] = TODAY()
)