Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a small dataset with years and quarters. So far it's small but will be growing to include more years.
2016 (Q4)
2017 (Q1, Q2, Q3, Q4)
The data contains new housing information. Below is an example of what I'd like to be able to compare year over year, quarter over quarter:
Housing starts
Vacant lots
Right now I simply did a quick measure to caluclate total housing starts for 2016 and 2017 then did a percent difference calculation. This works fine, but when we add more years I don't want to have to create all these measures.
Is there a table or visualization that would do all of this for me? Percent differences year over year and quarter over quarter and the ability to compare any of these is what I'm after.
Hopefully this makes sense.
Hi @Jodes,
You need to use the time intelligence to make this comparision sometihing like:
Current year = CALCULATE (TOTALYTD(SUM(Table[Column]);Table[Date])) Previous Year = CALCULATE (TOTALYTD(SUM(Table[Column]);DATEADD(Table[Date];-1;Year)))
This will calculate Year over year. but can also be made for quarter, month.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
My data does not have actual dates right now - it's simply entered like this:
Quarter Year
1 2016
2 2016
3 2016
4 2016
How would I add a date column that would be read by PowerBI? I'm guessing it might be tricky since my data does not have a specific day of the year. Rather, it's a range. Eg. Q1 = January 1 - March 30.
Or would I enter two separate columns perhaps? A start date and an end date.
Thanks for your help.
Hi @Jodes,
Looking at your setup add the following measures:
YTD = CALCULATE ( SUM ( Fact_table[Values] ); FILTER ( ALL ( Fact_table[Quarter]; Fact_table[Year] ); Fact_table[Quarter] <= MAX ( Fact_table[Quarter] ) && Fact_table[Year] = MAX ( Fact_table[Year] ) ) ) YTD PY = CALCULATE ( SUM ( Fact_table[Values] ); FILTER ( ALL ( Fact_table[Quarter]; Fact_table[Year] ); Fact_table[Quarter] <= MAX ( Fact_table[Quarter] ) && Fact_table[Year] = MAX ( Fact_table[Year] ) - 1 ) ) PY = CALCULATE ( SUM ( Fact_table[Values] ); FILTER ( ALL ( Fact_table[Quarter]; Fact_table[Year] ); Fact_table[Quarter] = MAX ( Fact_table[Quarter] ) && Fact_table[Year] = MAX ( Fact_table[Year] ) - 1 ) ) QTD vs PY = DIVIDE(SUM(Fact_table[Values])-[PY];[PY]) YTD vs PY = DIVIDE([YTD]-[YTD PY];[YTD PY])
This should do the trick:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelixThanks for this. Can you perhaps elaborate a bit further on how to implement this? I apologize - I'm not an advanced user by any means.
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |