cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sab-DFO New Member
New Member

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. 

 

Period Today Capture.PNG

 

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Display Fiscal Period FROM A MEASURE based on TODAY()

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]

12.png

 

Then create a measure

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

13.png

 

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 2
edhans New Contributor
New Contributor

Re: Display Fiscal Period FROM A MEASURE based on TODAY()

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()
)
Community Support Team
Community Support Team

Re: Display Fiscal Period FROM A MEASURE based on TODAY()

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]

12.png

 

Then create a measure

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

13.png

 

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.