Get filtered date from other table

[Anonymous]

 

 

I have a table with 2 date fileds financial date and transactional date and 3 cost amounts ,cost value,cost value adjust,cost value phy. The transaction date is in relation with date column from date dimension table.

There is a filter date field from date dimension. I need the following DAX for below sql.

case when (A.[Financial Date] <> '1900-01-01 ' and A.[Financial Date] <= '27/1/2020)

then (A.[Cost Amount] + A.[Cost Amount Adjustment]) else (A.[Cost Amount Physical]).

27/1/2020 should be dynamic when we pass 24/1/2020.

But i need the value of selectedvalue from slicer 

Should be the value from corresponding financial date not the value from date table.

Example, the financial date is 27/2/2020 , corresponding transaction date is 24/3/2020. When we 24/3/2020, it should take 27/2/2020 in the Dax.

I have around 7 million rows so just pasted a sample data.

Kindly help. Thanks in advance

[parry2k]

@Anonymous not sure what is changed, don't keep on changing the main post if you are making any change in the requirement, and the original question is answered, a mark that as solved and open a new post, it is a bit confusing going back and forth. You know your data and problem better, so it is not easy to understand if you keep on changing like that. Just a suggestion.

[Anonymous]

Sorry again! An extra filter is added and the id column is changed to warehouse column. A little change! 

[Anonymous · Accepted solution]

I created 2 measures as below.

Inventory1 =

VAR max_date=CALCULATE(MAX('Date'[Date]),ALL('Date'))

VAR END_DATE=ENDOFMONTH('Date'[Date])

VAR START_DATE=CALCULATE(min('Date'[Date]),ALL('Date'))

RETURN

CALCULATE(SUM('1 Inventory Value'[Cost Amount])+SUM('1 Inventory Value'[Cost Amount Adjustment]),FILTER('1 Inventory Value',('1 Inventory Value'[Financial Date]<=END_DATE && '1 Inventory Value'[Financial Date]>=START_DATE) && '1 Inventory Value'[Financial Date]<>DATE(1900,01,01)&& '1 Inventory Value'[Posting Type]

in{"Physical","Financial"}),ALL('Date'))

+

CALCULATE(SUM('1 Inventory Value'[Cost Amount Physical]),FILTER('1 Inventory Value',('1 Inventory Value'[Financial Date]<max_date && '1 Inventory Value'[Financial Date]>=START_DATE) &&('1 Inventory Value'[Financial Date]>END_DATE || '1 Inventory Value'[Financial Date]=DATE(1900,01,01))&& '1 Inventory Value'[Posting Type]

in{"Physical","Financial"}))

Inventory2 =

CALCULATE (

[Inventory1],

ALL ( 'Date'[Date]),

datesbetween( 'Date'[Date], blank(), lastdate('Date'[Date]))

)

And i used Inventory2 and it worked perfectly. Thanks to everyone for giving some hints.

[parry2k]

@Anonymous try this measure, change table and column name as per your model

Value = 
SUMX ( 
    VALUES ( Cost[Id] ), 
    IF ( CALCULATE ( MAX ( Cost[Tran Date] ) ) <= MAX ( 'Calendar'[Date] ),
    CALCULATE ( SUM ( Cost[Cost Amt] ) ) + CALCULATE ( SUM ( Cost[Cost Adj] ) ),
    CALCULATE ( SUM ( Cost[Cost Phy] ) ) )
) 

here is the output

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Apologies, I have made a little change in the requirement ,i tried the formula for previous requirement but its still running! Please check the new requirement which i updated and suggest.

[parry2k]

@Anonymous hey I would recommend posting sample data and expected output, it is a bit hard to understand what you are trying to do. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Sorry i am not getting the option to attach files here. I tried but i dont see the option.

[Anonymous]

Added .. Thanks

[richbenmintz]

Hi @Anonymous,

Are you able to provide

- sample data / sample pbix

- desired outcome visual or table

If  you can you will find that the fastest way to get your questions answered.

Thanks,

[Anonymous]

Added.. Thanks

[richbenmintz]

would you be able to paste the same data into a table?

[Anonymous]

Yes actually the data is from sql tables, i am just giving the sample. Modelling is done. Only thing is to get the Required Dax. I cant create a column with if condition as the dates are from different tables!

[richbenmintz]

Hi @Anonymous ,

Please paste sample data, without it we cannot create a model to test your use case with