Difficulty with Prior Year Measure up to Current Date

Question

Hello,&nbsp;I am trying to calculate YoY growth and prior year YoY growth in two measures for a Power BI report, and I am struggling to get Prior Year net billings up to the current date in order to then build these YoY growth rates.My Net Billings measure is simple:&nbsp;&nbsp;Net Billings = SUM('Trans Detail Data'[Net Billings Amount]).&nbsp;My PY Net Billings is configured as so:&nbsp;Net Billings PY = IF(NOT(ISBLANK([Net Billings])),CALCULATE([Net Billings],SAMEPERIODLASTYEAR('Date Dimension'[Date])))&nbsp;The hope of this construction of PY Net Billings was to bring forward only the prior year monthly net billings data to the present month (e.g. 202311).&nbsp; While this is successful for not calcualting the net billings forward from the prior year past the current month, the total at the bottom of the matrix still totals as if the entire prior year were brought forward.&nbsp; I have attached an image illustrate with sample data the issue present.&nbsp; The data is fake, and assumes we have achieved net billings of 1,000 each month from 202101 to 202311.&nbsp; The matrix only shows fiscal year 2024, which runs from August 2023 to July 2024.As you can see in this hypothetical matrix, while my Net Billings PY measure stops pulling forward data from the previous fiscal year in the rows, the TOTAL row still calculates as if all fiscal year 2023 months have been brought forward.&nbsp; I would expect the measure to only have sum'd the values present in the rows in the table.&nbsp; How could I rebuild/construct my Net Billings PY measure such that the total is only the sum of Net Billings for the first four months of fiscal year 2023 (August - November 2022)?

audreygerred · Answer

For TY you can use TOTALYTD DAX function, then for LY use CALCULATE([Your TY YTD Measure Name], SAMEPERIODLASTYEAR('Date'[Date]))

TOTALYTD function (DAX) - DAX | Microsoft Learn

Also, Marco Russo and Alberto Ferrari just published an article of using DAX for 'current' period: Computing MTD, QTD, YTD in Power BI for the current period - SQLBI

dangar332 · Answer

Hi,&nbsp;Anonymous&nbsp;try belowIF(
  NOT(ISBLANK([Net Billings])),
  CALCULATE([Net Billings],
  'tablename'[yearmaonth]= max('tablename'[yearmaonth])-12)
)

Forum Discussion

Difficulty with Prior Year Measure up to Current Date

2 Replies

Related content

Measure Difficulties

Authenticating OData.Feed using Web API difficulty

POWERBI LOGIN DIFFICULTY

Difficulty with measures

Difficulty with formulas