topic Re: TOTAL REV SPLY + 90d Calculation in DAX Commands and Tips

TOTAL REV SPLY + 90d Calculation

AlexAlberga727 — Fri, 07 Feb 2020 16:50:46 GMT

I'm looking for some assiatnce in the following -

I want to be able to calculate what my revenue was for [ (Today's Date LY) TO (Todays Date LY + 90d) ].

Example - If today was the first of the year, 01/01/2020. Then I want to know what my Rev. was for last year Q1 essentially. But this needs to be done dynamically for any date.

For today's calculation I would want the return to be total rev between 02/07/2019 - 05/08/2019.

Let me know if you have any thoughts, thanks!

I have failed with this below attempt -

SPLY+90D Rev = CALCULATE( [Total Revenue],

DATESBETWEEN( dDateTable[Date],

DATEADD( dDateTable[Date], -1, YEAR ),

( DATEADD(dDateTable[Date], -1, YEAR) + 90 ) ))

Re: TOTAL REV SPLY + 90d Calculation

AlexAlberga727 — Fri, 07 Feb 2020 18:50:33 GMT

Update -

I have created a calculated column that would calculate last years date, and another for laster years date + 90.

"Updated Date" Will just signify the last day the data was updated. Update should occurr daily.

SPLY = Today's date - 1 Year

SPLY +90 = [SPLY]+90

SPLY + 90 Total Rev = CALCULATE( [Total Revenue], DATESBETWEEN( dDateTable[Date], 'dInvVALUE (Keep Up-to-Date)'[SPLY], 'dInvVALUE (Keep Up-to-Date)'[SPLY + 90] ) )

However, still no success. Please let me know if you have any ideas.

Thanks again!

Re: TOTAL REV SPLY + 90d Calculation

Anonymous — Mon, 10 Feb 2020 06:05:24 GMT

hi AlexAlberga727 ,

please try the DAX formula to make MEASURE :

Rev Sply = VAR Today_LY = TODAY () - 365 VAR LY_90 = Today_LY + 90 RETURN CALCULATE ( Sum_revenue, DATESBETWEEN ( 'Table'[date], Today_LY, LY_90 ) )

NOTE : make sure to change Date column to date type otherwise you will end up with the same issue you are facing.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.

Regards,

Gaurav Raj Singh

LinkedIN : https://www.linkedin.com/in/gauravrajsingh/

Re: TOTAL REV SPLY + 90d Calculation

AlexAlberga727 — Mon, 10 Feb 2020 13:56:33 GMT

Thank you very much!