Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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
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,
Proud to be a Super User!
Added.. Thanks
would you be able to paste the same data into a table?
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 @Anonymous ,
Please paste sample data, without it we cannot create a model to test your use case with
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |