Compare two different periods of time on a single visual

[DataNinja09]

Hello everyone,

I am struggling with one of my client's need.

Indeed, he uses a KPI of average occupancy of its offices per half day (morning/afternoon), named OPM.

He wants a visual with days of the week as one axis and this KPI value on the other where one line value would be the average of every day (Monday, Tuesday, etc.) over a selected period of time (so, average of Mondays, average of Tuesdays and so on)

and the other line value would be the same but only for a selected week.

It would allow him to compare this KPI of a specific week to a typical week (over a specific period).

I tried it with an ALL function on the Week field for the "typical OPM" measure but what happens is if I add a fliter on the page with the field "Date" to select the period for the typical OPM it filters both of the measures, like it cancels the filter on the week selected for the "specific week OPM"

Does someone have a lead on how to do it?

Many thanks in advance for your precious help.

[theov]

One of the best ways is to create comparative table, the user will have the possibility to choose any two time perdiods and the single visual will show the comparison :  

Here are two good videos explaining this subject fully:

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

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

[DataNinja09 · Accepted solution]

I actually found the solution, very well explained by Alberto! 🙂

https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

Thank you guys for your help!

[v-cherch-msft]

Hi @DataNinja09

You may use ALLSELECTED Function (DAX) for your measures.

Regards,
Cherie

[DataNinja09]

Hi,

Thank you for your answer.

Here is what I currently have :

What I currently have

I would like that the Date filter filters the "OPM Type" line and the Week filter to filter the "OPM" line.

But as soon as I use the Date filter it filters both lines and they just join in one unique line.

Here is my source table :

My source table

The two lines are measures calculated as follows :

OPM = DIVIDE(AVERAGEX(SUMMARIZE(FT_GILIF_CONS;FT_GILIF_CONS[Date et Heure];"Demi-journée_moy";AVERAGE(FT_GILIF_CONS[Valeur]));[Demi-journée_moy]);100)

OPM type = CALCULATE([OPM];ALL(D_Date[Week details]); ALL(D_Date[Week]))

[DataNinja09]

To be a bit more clear. Every "PDT" is a desk and "Valeur" tells whether or not this desk was occupied during this half day (Date et Heure). OPM is simply the average of these "Valeur" values so that we know the percentage of them that were occupied.

We need to compare for example : how did I occupy my desk last week compare to the occupancy of all the weeks of March 2018 combined.

[Anonymous]

Can you post the data columns sample explain what they are and what you want to do with them please and thank you.

[DataNinja09]

Hello everyone,

I am struggling with one of my client's need.

Indeed, he uses a KPI of average occupancy of its offices per half day (morning/afternoon), named OPM.

He wants a visual with days of the week as one axis and this KPI value on the other where one line value would be the average of every day (Monday, Tuesday, etc.) over a selected period of time (so, average of Mondays, average of Tuesdays and so on)

and the other line value would be the same but only for a selected week.

It would allow him to compare this KPI of a specific week to a typical week (over a specific period).

I tried it with an ALL function on the Week field for the "typical OPM" measure but what happens is if I add a fliter on the page with the field "Date" to select the period for the typical OPM it filters both of the measures, like it cancels the filter on the week selected for the "specific week OPM"

Does someone have a lead on how to do it?

Many thanks in advance for your precious help.

[Anonymous]

Try This

Day Average = CALCULATE(AVERAGEX(Sales,Sales[Sales]),ALLEXCEPT(Sales,Sales[Day of Week]))

[DataNinja09]

Thank you for your answer,

Unfortunately it doesn't work as if I include "DayofWeek Name" in the ALLEXCEPT, the "Week OPM" is not filtered anymore on the specific week I select. And if I include "Week" in the ALLEXCEPT it is not balanced anymore among the days of the week (I have the same value for every day).

If I include both it does the same as it used to (one unique line as they are both filtered)

[Anonymous]

Have a look at this screenshot, I added weeknumber and it still works. I am not sure the calculations you are using or how your today model is, could you send me the power bi file of what you are doing so I can pinpoint your error.

[DataNinja09]

I have put more details on another post if you don't mind check it out, I added pictures and all.

[Anonymous]

umm, the screenshot i sent you is working using including dayofweek and filtering on day, i can change that to week number and I think it will still work. Including week number won't work. 

Is your week opm coming from another table or is it in the same table?