Filter Measure on Date, created by summarize date

[StatStories_Lon]

Dear all,

My name is Ruud and I posted last week a topic in which I had a problem. That problem was solved to a certain extent and I have put the topic to solved. I could not un-solve the topic, or I did not know how to. Nevertheless, I occured a problem when I tried to filter the solution on date. The measure wouldn't filter on dates. For example when I put the measure in a Card and specifiy on particular dates, it would not change with the date. In other words, the total amount was still there. It worked when I tried to filter on users.

The specific topic:

https://community.powerbi.com/t5/Desktop/Calculate-Sum-Total-filter-by-contract-start-and-end-date-p...

The solution:

Measure =
VAR _tbl = SUMMARIZE(Dim_Contract; Dim_Contract[ContractId]; Dim_Users[UserId]; Dim_Contract[Start_Date]; Dim_Contract[End_Date]; "Qtde Hours";
CALCULATE(SUM('Fact_Target Hours'[Target Hours]); FILTER(ALL('Fact_Target Hours');
'Fact_Target Hours'[medewerker_id] = EARLIER(Dim_Users[UserId]) &&
'Fact_Target Hours'[Datum] >= EARLIER(Dim_Contract[Start_Date]) &&
'Fact_Target Hours'[Datum] <= EARLIER(Dim_Contract[End_Date])
))
)
RETURN SUMX(_tbl; [Qtde Hours])

The context

My name is Ruud and I am preparing a dashboard for an external company. Because of privacy, I have prepared a similar data set that shows the problem that I have at the moment.

Context: for this company I am trying to calculate the total sum of hours worked, and the total sum of target hours that one has to work in a specific period.

The first one is not a problem, however the second one I have troubles with by the way they register their data.

The problem: In the targeted hours, they include hours for each day for the whole year (except for weekend days). The problem occurs when a person does not work the whole year, for the reason that he or she leaves the company or enters the company at a specific moment during the year.

What I would like to have:

I want a measure that calculates the sum of total targeted hours per person with their specific contract- start and end date. In other words, if a person started after the first date in the calendertable, then the targeted hours present before this date should not be included.

Likewise, if the end date is before todays date, then the hours after the contract_end date to todays moment should now be included.

The file:

In the Data I focused my problem on user ‘A1’. His contract date starts at 05-03-2020 and end beyond our todays date. However, the targeted hours start counting from 05-01-2020. Therefore, there are two months included in the sum of targeted hours that should not be there.

Thank you for your time,

Cheers,

Ruud Scheijen

https://drive.google.com/file/d/1KfPdIVPzgqQr-YXyGGNz5kaRYn_4JrI3/view?usp=sharing 

[V-lianl-msft]

Hi @StatStories_Lon ,

Try to create a measure using the if statement and apply it to the visual level filter.

Measure 2 = IF(MAX(Dim_Date[Date])>=MAX(Dim_Contract[Start_Date])&&MAX(Dim_Date[Date])<=TODAY(),1)

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

[StatStories_Lon]

Thank you for your message.

Try to create a measure using the if statement and apply it to the visual level filter.

Measure 2 = IF(MAX(Dim_Date[Date])>=MAX(Dim_Contract[Start_Date])&&MAX(Dim_Date[Date])<=TODAY(),1)

I tried this, however, when I set measure2 as a visual filter I could not change its values. Whereas when you can change it it turns gray, in mine it is still white.  See the picture below

Cannot change the measure2 filter value.

[V-lianl-msft · Accepted solution]

Hi @StatStories_Lon ,

Sorry, I may have misunderstood you.

Please replace the filter condition "all" of the measure with "ALLSELECTED".

Measure = 
VAR _tbl = SUMMARIZE(Dim_Contract, Dim_Contract[ContractId], Dim_Users[UserId], Dim_Contract[Start_Date], Dim_Contract[End_Date], "Qtde Hours",
CALCULATE(SUM('Fact_Target Hours'[Target Hours]), FILTER(ALLSELECTED('Fact_Target Hours'),
'Fact_Target Hours'[medewerker_id] = EARLIER(Dim_Users[UserId]) &&
'Fact_Target Hours'[Datum] >= EARLIER(Dim_Contract[Start_Date]) &&
'Fact_Target Hours'[Datum] <= EARLIER(Dim_Contract[End_Date]) &&
'Fact_Target Hours'[Datum] <= TODAY()
))
)
RETURN SUMX(_tbl, [Qtde Hours])

What really puzzles me is here

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

[StatStories_Lon]

Dear Liang,

Thank you so much! Your suggestion was the solution that I was looking for.

Regarding the contractsituation for person A4: this is probably a mistake made that I overlooked when wanting to upload the sample dataset.

Best,

Ruud

[MFelix]

Hi  @StatStories_Lon ,

First of all to unselect an answer from being correct go to the post that you consider as correct click the 3 dots on the rigth and choose the option Not The Answer.

Regarding you post to what I can see the calculations are correct you have the A1 user from 05/03/2020 until the 19/03/2020 the sum of the target hours are 140 based on the Fact target hours so eveyrthing is matching ot what I can see.

Can you please explain a little bit better what you need and what is the result you are trying to achieve?

[StatStories_Lon]

Thank you for your reply

Can you please explain a little bit better what you need and what is the result you are trying to achieve?

>> In the card "Measure" there is the total sum of target hours. However, when I want to apply a filter on this visual that selects only 11 March, then that measure does not change accordingly. (see picture). 

What I try to achieve is that when I select a (range of) date(s), that this measure is changing accordingly.

However, it changes when I filter it by user_id. Selecting a Single date does not change "Measure"However, selecting not A1 but A2 does.