Writing Dax Measure by comparing 2 columns from different tables

[Anonymous]

Hello,

I have created dummy tables for a good understanding. There are 2 tables : DimDate and Fact_Storage.

Relationship

Dim_Date

Fact_Storage

Dashboard

 Here DimDate table has a Datekey. Fact_storage  table has datein key and Dateout key. It shows material was delivered on datein key and material was sold on dateoutkey. Storage location shows where the material was stored until it was sold.

Datekey from DIm_Date is connected to DateinKey from Fact_Storage.

My goal is to create a measure to calculate the Material Weight when the Date from Dim_Date is filtered. 

The criteria is if the date is filtered ( For example 12.01.2023), then i need to calculate tha material weight from datein 09.01.2023 till datein 12.01.2023, if the material is still in storage location.  That means when i select 12.01, i should get the total weight 300KG which comes from Material B and Material C, which have not Dateout information.

Or in other way i need to calculate Materialweight by using these filters. 
DateInKey < Datekey

DateOutKey > Datekey.  Can i used these 2 criteria in a measure to get my result.

I would really appreciate, if someone could help me. thanking you in advance.

[amitchandak · Accepted solution]

@Anonymous , Seem like very similar to HR

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Or the attached file

[marcelsmaglhaes · Accepted solution]

Hi @Anonymous 

I don't know if I understand your needs, but you can write a mesaure like that:

Measure =

VAR __DATE = SELECTEDVALUE(d_date[data_key])

VAR __CALC =

CALCULATE(

            SUM(f_storage[Weight]),

            ALL(f_storage),

            f_storage[data_in_key] < __DATA && f_storage[date_out] = BLANK())

RETURN

__CALC

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

Kind Regards,
Marcel Magalhães

[marcelsmaglhaes · Accepted solution]

Hi @Anonymous 

I don't know if I understand your needs, but you can write a mesaure like that:

Measure =

VAR __DATE = SELECTEDVALUE(d_date[data_key])

VAR __CALC =

CALCULATE(

            SUM(f_storage[Weight]),

            ALL(f_storage),

            f_storage[data_in_key] < __DATA && f_storage[date_out] = BLANK())

RETURN

__CALC

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

Kind Regards,
Marcel Magalhães

[Anonymous]

Hi @marcelsmaglhaes ,
Thank you for your reply.

It is not working becasue if i use this measure _Date =SELECTEDVALUE(DimDate[DateKey])
Then it shows a cumulative sum of the DateKey something like 20.3 Million. Its not showing exact DateKey 20230112, if i have selected 12.01.2023 as a filer from Dim_Date.

The main goal is :
If i select 12.01.2023 as a filter from DimDate, then i need to sum all the dates where DateOut is blank till 12.01 (also 10.01 and 09.01).
That means the sum should be 300KG ( Material B&C as they dont have DateOut). The DateOut key by material B is written by mistake.

Do you have any other suggestions? Thanks in Advance 🙂

[marcelsmaglhaes]

Hey @Anonymous ,

If you need to filter a date, then selectedvalue captures the selected date and will serve as the date to look at the fact table to search and filter the values. Of course this measure needs to be improve according your visualisation needs, but is the starts.

[Anonymous · Accepted solution]

Thnx @marcelsmaglhaes @amitchandak ,

The use of Crossfilter and selected value both worked. 

This measure worked, 

var dateshow = SELECTEDVALUE(DimDate[DateKey])

return

CALCULATE(SUM(FactStorage[MaterialWeightKG]),

CROSSFILTER(DimDate[DateKey], FactStorage[DateInKey], None),

FactStorage[DateInKey]<=dateshow && (FactStorage[DateOutKey]>dateshow || FactInventory[DateOutKey]=BLANK()))

[amitchandak · Accepted solution]

@Anonymous , Seem like very similar to HR

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Or the attached file

[Anonymous]

Hello @amitchandak ,

Thank you for the reply. Yes i also tried the same way as you have shown in the video. I get the values correct. But my goal is to get the value with a date selected. For eg.  In my case, i want to select date as 12.01 and the sum of material_KG should be 300 (sum of 10.01 & 11.01 as those material are having no dateOut).
That means selecting date as 12.01 in filter , i need to look the values before 12.01 too. I tried using All but didnt work. Do you have any suggestions for me ? 

Thanking you again in Advance 🙂