Day YoY measure

[michael_nguyen]

Hi PBI Community - 

I'm creating a dashboard with YTD, MTD, WTD and current day, I'm having an issue with the Daily YoY % change. The values are blank... any help would be appreciated, below are the formuals I used and a screenshot of the results.

daily Rev = 
var _year = year(TODAY())
var _month = MONTH(TODAY()) 
var _day = day(TODAY())

return
CALCULATE (
       [amount],
        FILTER (
           ALLSELECTED ('date'),
                'date'[Date]=DATE(_year,_month,_day)))

daily LY Rev = 
var _year = year(TODAY())-1
var _month = MONTH(TODAY()) 
var _day = day(TODAY())

return
CALCULATE (
       [amount],
        FILTER (
           ALLSELECTED ('date'),
                'date'[Date]=DATE(_year,_month,_day)))

Day YoY% Rev = 
DIVIDE(([daily Rev]-[daily LY Rev]),[daily Rev])

[amitchandak · Accepted solution]

When you are using today and today - 1 year. Till only give you revenue for those dates.

Your simple sum of amount and the same period last year will give you that on a daily basis. Using the calendar you can view same at daily, month , qtr or for an year

This period = CALCULATE(sum(Sales[Sales Amount]))

last year same period = CALCULATE(sum(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date Filer]))

There are other options for the month, qtr and year like datesmtd, totalmtd etc

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date Filer],-1,MONTH)))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date Filer])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],-1,QUARTER)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date Filer])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-1,Year)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[mwegener]

Hi @michael_nguyen ,

can it be that your "daily Rev" is blank?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[michael_nguyen]

@mwegener  thanks for the response. There is daily revenue.