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.
Hello All,
Would need your help in the DAX running total for the below scenario (running total for the MTD - month to date and PM - previous month):-
My Dax:
RunningTotal =
VAR
CurrentDay = MAX ( 'Data Source'[Weekday] )
RETURN CALCULATE (
SUMX ( VALUES ( 'Data Source'[Weekday] ), sum('Data Source'[Standard Quantity KG 3rd Party]) ),
'Data Source'[Weekday] <= CurrentDay
)
First time I posted my question here, thanks for your kind assistance.
Unable to send the BI file over here, do let me know should you need it.
Thanks!!
Solved! Go to Solution.
Hello friend
This shall do it
Cumulative = SUMX ( FILTER ( ALLEXCEPT ( 'Data Source', 'Data Source'[Reformat], 'Data Source'[SH - Country docum.] ), 'Data Source'[Weekday] <= SELECTEDVALUE ( 'Data Source'[Weekday] ) ), CALCULATE ( SUM ( 'Data Source'[Standard Quantity KG 3rd Party] ) ) )
Try this Measure
Cumulative = IF ( HASONEVALUE ( 'Data Source'[Weekday] ), SUMX ( FILTER ( ALL ( 'Data Source' ), 'Data Source'[Weekday] <= VALUES ( 'Data Source'[Weekday] ) ), 'Data Source'[Standard Quantity KG 3rd Party] ) )
Dear Zubair,
It is still not working. This is my first time publish my issue here, do you know how to share the pbix file over here, it would be more efficient in this way.
Hi @tanct
You can upload your file to onedrive or google drive and share the link here using "insert/edit link" option
https://drive.google.com/file/d/0B_Wuqq42mXSEcVNJalBydGFyQmM/view?usp=sharing
Many thanks for your reply and prompt reply, could you please click on the link for PBIX download for your shared expertise.
Thanks@!
Hello friend
This shall do it
Cumulative = SUMX ( FILTER ( ALLEXCEPT ( 'Data Source', 'Data Source'[Reformat], 'Data Source'[SH - Country docum.] ), 'Data Source'[Weekday] <= SELECTEDVALUE ( 'Data Source'[Weekday] ) ), CALCULATE ( SUM ( 'Data Source'[Standard Quantity KG 3rd Party] ) ) )
Hi @estewart,
Sorry for the delay.
To achieve your requirement, you could try the steps below.
1. Duplicate the table in Query Editor and filter the row to only show the records like below.
2.Then Apply and Close, create the Index column for the two tables with IF function in Data View.
Index = IF('Table1'[Variable] in {"Trend_1","Trend_1_Start"},1,2) Index = IF('Table2'[Variable] = "Trend_1_Start",1,2)
3. Create the lookupvalue calculated column in Table1.
lookupvalue = LOOKUPVALUE ( 'Table2'[Value], 'Table2'[YearQuarter], 'Table1'[YearQuarter], Table2[Index], 'Table1'[Index] )
Here is the output.
More details, please refer to the attachment.
Best Regard,
Cherry
Zubair, thanks so much for your prompt support, it was resolved less than few hours after I posted my issue here.
Strongly recommend !
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |