Unable to compute Yearly data and %YoY from a monthly

[pey]

Hi, I have the following dataset:

I have a monthly home sales data and have managed to computed the %YoY change by creating another measure (Prev_Total Home Sales) using the following DAX Formula:

Prev_Total Home sales = CALCULATE([Total home sales _measure],DATEADD(Housing[Date],-1,year)).

However, I am not able to computer the %YoY on the YEARLY total homesales. Did anyone encounter such issue? Would like to request for some help on this!!

Monthly data

Yearly data

Yearly data obtained by selecting "Year" in the Date hierachy. 

[pey]

It works perfectly now! Thank you so much! 

[themistoklis]

@pey 

That's strange. How do you calculate the [Total home sales _measure] measure?

You can try the following fomulas (all are measures)

YTD = CALCULATE(SUM(Table[Sales]),DATESYTD(Housing[Date]))

YTD LY = CALCULATE(Table[YTD],SAMEPERIODLASTYEAR(Housing[Date]))

Measure = ([YTD] - [YTD LY]) / [YTD LY]

[pey]

Thanks for the prompt response.

Total home sales _measure = CALCULATE(SUM(Housing[Total home sales])).

Tried your method below (shown in second table) but still yield the same result..

[themistoklis]

@pey 

Can you share the workspace with us? Mask any sensitive data

Do you use a Calendar Date table? I believe the issue has to do with dates not being continuous in the dataset.

Trying adding a Calendar table, join it with the main table on date field, and instead of using Housing_Date in formulas .. use the Calendar date instead

[pey]

Hi,

Can you try the following link:

https://app.powerbi.com/reportEmbed?reportId=586c1606-ec9a-40eb-bc2e-b1e86eb2ddf2&autoAuth=true&ctid...

Let me know if you have any issue accessing the link. I am having administrative issue with the "publishto public" option..

[v-kkf-msft · Accepted solution]

Hi @pey ，

I create a date table, and a relationship between Dates table and Housing table.

Dates = CALENDAR(DATE(2015,1,1), DATE(2017,12,31))

The use the following measure:

Prev_Total Home sales = 
CALCULATE(
    [Total home sales _measure], 
    ALL(Dates), 
    DATEADD( Dates[Date], -1, year )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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