Newbie to DAX, working for years from Excel, need some help

[Yahya]

Dear Community,

I am in desperate need of help.

I have loads of experience in Excel, but started recently working with Power BI. I've had my share of formula's in Excel, and I'm learning DAX which I think is extremely powerful, but sometimes quite complex.

I'm looking for a solution that I had very simply resolved in Excel with a SUMIF but can't figure out in DAX.

So I have the following measure:

Average Headcount from Period 2020/01 & 2020/02 =

CALCULATE (

[Sum of Total Hrs];

FILTER('Dbase pivot';'Dbase pivot'[Intervention End Period] = "2020/01"

|| 'Dbase pivot'[Intervention End Period] = "2020/02"

&&( 'Dbase pivot'[Intervention End Day] = "Monday"

|| 'Dbase pivot'[Intervention End Day] = "Tuesday"

|| 'Dbase pivot'[Intervention End Day] = "Wednesday"

|| 'Dbase pivot'[Intervention End Day] = "Thursday"

|| 'Dbase pivot'[Intervention End Day] = "Friday")

)/(7,6/252*218)/8

This work perfectly as inttended and is showing me the result I want per contract per day from the period 01/2020 & 02/2020

Contract Nr	Monday	Tuesday	Wednesday	Thursday	Friday
M0049A02	0,26	0,40	0,61	0,60	0,63
M0056A01	26,08	20,27	17,18	18,97	19,30

Now, the next step is where I have some issues:

I have a measure that does a simple calculation

Headcount Per Day = SUM(Dbase[Headcount])

The headcount of 26/03/2020 is 0,89

and I need to do the difference between Headcount Per Day & Average Headcount from Period 2020/01 & 2020/02

So I simply thought this would work: Headcount Deviation = Headcount Per Day - Average Headcount from Period 2020/01 & 2020/02 but the result is 0.89 - 0 and not 0,89 - 0,60 (since we're thursday)

I have to know where I'm going wrong, but I suppose it's because I'm trying to compare today's headcount with the average of the past and that, since I'm in March, it filters it out and there's a null value.

How can I fix this ?

[Stachu]

how do you filter for 26/03/2020? is it coming from slicer or is it based on TODAY()?

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

[Yahya]

Thanks for the reply.

So I have a pretty large dataset(about 1,2Mi lines) but I shared in the following link an excel sheet which is confidential friendly and GDPR friendly limited to columns you may need.

Dataset Sample 

Thanks for having a look!

Yahya