Calculate New Revenue for a certain area that has not had any for the past 3 years from today?

[milkmoneymike]

Hello All

I am trying to show the Sum of the amount for each area and year specifically I would like to show the areas that have generated revenue this year but have not generated revenue in the past 3 year from today. I can calculate the sum of the past three years but i can seem to figure out the how to filter areas that have revenue for prior years. Here are the columns im working with,thank you!

thank you for any help

[pranit828]

HI @milkmoneymike  

If areas that have generated revenue this year

'but have not generated revenue in the past 3 year from today'

How is it possible that generated revenew this year but did not on past 3 years from today.

If we consider taday is this year it generated revenew. Past three years from today dosn't make any sense.

Please give an simple example from your data set lets say for country 

Azerbaijan

What would you expect in the new measure or column.

[milkmoneymike]

My apologies 

This was meant to be 

Show the revenue for current year if the past 3 years have had no revenue. So it will show any area with a revenue in 2020 and no revenue in 2019-2017.

[Icey · Accepted solution]

Hi @milkmoneymike ,

How about this?

Measure =
VAR thisyear =
    YEAR ( TODAY () )
VAR thisyearrevenue =
    CALCULATE ( SUM ( 'Table'[ExtendedAmount] ), 'Table'[Year] = thisyear )
VAR last3yearsrevenue =
    CALCULATE (
        SUM ( 'Table'[ExtendedAmount] ),
        FILTER ( 'Table', [Year] >= thisyear - 3 && [Year] < thisyear )
    )
RETURN
    IF ( ISBLANK ( last3yearsrevenue ) && NOT ( ISBLANK ( thisyearrevenue ) ), 1 )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@milkmoneymike , Try with date calendar

past three year

Rolling 36 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today,-36,MONTH))

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today,-3,Year))

codition  if( CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today,-3,Year))+0 = 0 && CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) >0 , 1,blank())

or

if( CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],today,-3,Year))+0 = 0 && CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) >0 , 1,0)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.