Measures to reference "Current" period

[DickyMoo]

Hello,

I am trying to create measures that show calculations for current / previous periods.

I'm in accounting, and we work in periods, so the "current" month is not always the current period. Year-end is June 30th, so we are in period 12 now, but would like to display results for May, period 11.

I would like to create a measure that specifies 2016011 as the period I would like the report to consider current, I can then update this to 2016012 when required.

I know I can use CALCULATE( [measure] , Period = 2016012 ) , but I have lots of such measures, so would like an alternative.

Any advice appreciated.

Thanks
Rich

 

[akice]

Do you have a separate Calendar table? what are you working in - Excel? Power BI Desktop? are your dates stored as text in Fact table?

 

[DickyMoo]

I am using Power BI Desktop. I have a separate calendar table created in Power Query, linked to the fact table.

 

[akice]

How is your pivot organized? Periods on rows or columns? or just gl accounts ? without knowing details it is hard to answer.

 

[DickyMoo]

I am using a hierarchy structure, so showing figures for Bank charges, Staff costs etc. It's a work in progress so I don't know what visualisations I will be using. Important figures are actuals and budget for current month and year to date (i.e. year to end of previous month).

DAX is very good at time-intelligence, but I can't use TOTALYTD or similar because they will look at all data in the current year, whereas I would like to explicitly state when the cut-off is.

Power BI has recently introduced parameters for queries, and I suppose I'm after something similar; a centrally defined value that can be used in many measures, and only needs updating once.

 

[Dan80]

In your dates table, could you do a calculated column that says:

IF([Fin Yr & Pd]=MAX('Actuals table'[Fin Yr & Pd]),"Current","Historic")

Presumably you are pulling actuals into a table and so P11 is the last period of data? If so, I use something similar to the above for my report. Hope you can get it to work!

 

[DickyMoo]

That's a good idea, but unfortunately data for P12 starts coming through from the first day of the month.

 

[DickyMoo]

I have managed to locate the table in the database that holds the open / current period information, so I will reference that in my measures.

Thanks for the replies guys.