cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Get filtered date from other table

Query12.PNG

 

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

1 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.

View solution in original post

13 REPLIES 13
Super User III
Super User III

@Rajashekhar 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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

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.

View solution in original post

Super User III
Super User III

@Rajashekhar 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

 

parry2k_0-1611167070569.png

 

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.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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.

Super User III
Super User III

@Rajashekhar 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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

Added .. Thanks

Super User I
Super User I

Hi @Rajashekhar,

 

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,





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Added.. Thanks

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

Hi @Rajashekhar ,

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors