2 Year Revenue Comparison Clustered Column Chart that is Dynamic?

[caleb1208]

I would like a clustered column chart that shows the selected year's revenue and the previous year's.

So for example, if I choose FY 20 YTD, it shows total revenue for both FY20 and FY19, if FY19, both FY19 and FY18, and if FY18, it will show just FY18 data (since there is no more previous year comparative data)

IF( [Selected Year]="FY20", CALCULATE(SUM('Data from National'[Revenue]), FILTER('Data from National', 'Data from National'[Fiscal year]="FY20 YTD"||'Data from National'[Fiscal year]="FY19")),IF( [Selected Year]="FY19", CALCULATE(SUM('Data from National'[Revenue]), FILTER('Data from National', 'Data from National'[Fiscal year]="FY19"||'Data from National'[Fiscal year]="FY18")), CALCULATE(SUM('Data from National'[Revenue]), FILTER('Data from National', 'Data from National'[Fiscal year]="FY18"))))

[v-juanli-msft · Accepted solution]

Hi @caleb1208 

Create tables

date = ADDCOLUMNS( CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

add columns
fiscal year = IF([month]>=7,[year]+1,[year])

fiscal month = IF([month]>=7,[month]-6,[month]+6)

FY = "FY"&RIGHT([fiscal year],2)

FY = SUMMARIZE('date',[FY],[fiscal year])

Add FY[FY] into a slicer,

Create measures

Measure = SELECTEDVALUE(FY[FY])

Measure 2 =
CALCULATE (
    SUM ( 'Table'[sales] ),
    FILTER (
        'date',
        'date'[fiscal year]
            = SELECTEDVALUE ( FY[fiscal year] )
            || 'date'[fiscal year]
                = SELECTEDVALUE ( FY[fiscal year] ) - 1
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@caleb1208 ,

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

2nd Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

2nd last year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-2))

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.

[parry2k]

@caleb1208 you should create a fiscal year calendar and previous year time intelligent functions to get the numbers rather than writing this complex measure

here are a few links for you:

https://www.youtube.com/watch?v=Whu9FZZorpc

https://www.youtube.com/watch?v=7l1M3iSTx1c

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!