combine SAMEPERIODLASTYEAR with DATEADD

[dfox09]

Is it possible to combine SAMEPERIODLASTYEAR with DATEADD? Our Fiscal Year starts in May and ends in April therefor I can't use the normal forecasting methods as those companies with a normal calendar year as their Fiscal year. Is it possible to set up a formula in DAX that would look at the current month and calculate the average revenue between the proceeding month in a prior Fiscal Year and the last month of the prior Fiscal Year? For example, if I am going to forecast July (2020) revenue, I would take the average of each monthly revenue from August, 2019 (the proceeding month of the prior Fiscal year) through April, 2020 (the last Fiscal month of the prior Fiscal Year. 

I can do this in Excel but haven't figured out how to write something like this in DAX.

Here is the DAX formula that I came up with but it doesn't work.

Production Forecast =

VAR _Start_date = SAMEPERIODLASTYEAR(DATEADD(DimAccountingPeriod[BeginDate].[Month],1 ,MONTH))

VAR Cur_prod = 'Measures Table'[Production] - not able to use in formula below.

VAR _End_date = DATE(2020, 04,30)

RETURN

            CALCULATE(

AVERAGEX('TBL_Actuals',TBL_Actuals[Producer Revenue]),

DATESBETWEEN(DimAccountingPeriod[BeginDate], _Start_date , _End_date)

)

Any help would be appreciated!

Don

[Greg_Deckler]

@dfox09 - See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[dfox09]

Hi Greg,

Thank you for replying. As a newbie, I got stuck when attempting to apply your solution to my issue. A couple of questions;

Within the first measure, Is T'heHardWay' created on the fly? DAX couldn't find the table (of course, since it didn't previously exist).

I created my own date but still don't know how to write the DAX formula to calculate a forecast given the constraints of our Fiscal Year.

Don