Comparing data easily

[Jodes]

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.

[MFelix]

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

[Jodes]

Hi @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.

[MFelix]

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

[Jodes]

@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.