Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a running total of assets by month and this is working fine.
Here is my formula for the running total
RT Total =
VAR __Date = MAX(MasterDate[Dates])
VAR __Table = FILTER(ALLSELECTED(MasterDate),MasterDate[Dates] <= __Date)
RETURN
SUMX(__Table,[Total])
Total measure is
Total = CALCULATE(SUM(glentry[amount]))
The average should be
22,393,778.19
Everything I have tried will not work.
What DAX formula will give me this result?
Hi,
I am not sure how the semantic model looks like, but please try something like below whether it suits your requirement.
Running average =
VAR __Date =
MAX ( MasterDate[Dates] )
VAR __Table =
SUMMARIZE (
FILTER ( ALLSELECTED ( MasterDate ), MasterDate[Dates] <= __Date ),
MasterDate[YearMonth Sort Column]
)
RETURN
AVERAGEX ( __Table, [Total] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I tried what you suggested
Average Invnetory =
VAR __Date =
MAX ( MasterDate[Dates] )
VAR __Table =
SUMMARIZE (
FILTER ( ALLSELECTED ( MasterDate ), MasterDate[Dates] <= __Date ),
MasterDate[YearMonth]
)
RETURN
AVERAGEX ( __Table, [Total] )
But it returns 2.49 million
Here is my master date table
Could it be because of this blank year month value?
I found the issue with the blank value and removed it. this is what I have when I look at YearMonth with my CurrentInventory Total and CurrentInventory Running total
This is my Dax based on your suggestion for running total
Average Invnetory =
VAR __Date =
MAX ( MasterDate[Dates] )
VAR __Table =
SUMMARIZE (
FILTER ( ALLSELECTED ( MasterDate ), MasterDate[Dates] <= __Date ),
MasterDate[YearMonth]
)
RETURN
AVERAGEX ( __Table, [RT Current Inventory])
RT Current Inventory is
RT Current Inventory =
VAR __Date = MAX(MasterDate[Dates])
VAR __Table = FILTER(ALLSELECTED(MasterDate),MasterDate[Dates] <= __Date)
RETURN
SUMX(__Table,[Current Inventory])
This gives me 19326079.35 which is the average of all years.
So its the correct amount for all years but when I filter to 2023 i get this value
What I would like it the average for the currently selected year. which should be 22,393,778.19 in 2023.
User | Count |
---|---|
89 | |
84 | |
65 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |