Display Fiscal Period FROM A MEASURE based on TODAY()

[Sab-DFO]

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 !

[v-juanli-msft · Accepted solution]

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]

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()
)