Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have table name called Invenoty sheet . this table look like . based on the wknum selection will show rolling average to next 4 weeks .
DATE ATTRIBUTE wknum value
15 March 2021 Demand wk01 45
22 March 2021 Demand wk02 55
29 March 2021 Demand wk03 65
05 April 2021 Demand wk04 45
12 April 2021 Demand wk05 95
19 April 2021 Demand wk06 25
26 April 2021 Demand wk07 35
I am Used below Measure
Expected Output :
wknum ATTRIBUTE value Selected week from Next 4 week
wk01 Demand 45 220 => 55+55+65+45
wk02 Demand 55 260 => 55+65+45+95
wk03 Demand 65 250 => 65+45+95+45
wk04 Demand 45 240 =>45+95+45+55
wk05 Demand 95 195 =>95+45+55
wk06 Demand 45 90 =>45+55
wk07 Demand 55 55 =>55
Looking for support .. thanks in advance .
Solved! Go to Solution.
Hi @THENNA_41
You already have a Date table, it is better to use it. Please refer to attached file with the proposed solution
@tamerj1 its working perfectly . one more questions how to convert above measure SUM to average ?
@tamerj1 its working Now perfectly . I have one more question How to convert above measure SUM to average ?
Hi @THENNA_41
If [wknum] column is from the 'Date' table then it should work with a small modification
Mesaure =
CALCULATE (
SUM ( Append1[Value] ),
Append1[Attributes] = "Demand",
DATESINPERIOD ( 'Date'[Date], MIN ( 'Date'[Date] ), 1, MONTH ),
ALLSELECTED ( Append1 )
)
Hi @THENNA_41
Please try
Mesaure =
VAR CurrentDate =
CALCULATE (
MIN ( 'Date'[Date] ),
CROSSFILTER ( 'Date'[Date], Append1[Date], BOTH )
)
RETURN
CALCULATE (
SUM ( Append1[Value] ),
Append1[Attributes] = "Demand",
DATESINPERIOD ( 'Date'[Date], CurrentDate, 1, MONTH ),
ALL ( Append1 )
)
@tamerj1 i have tired above measure .. but i am getting CROSSFILTER function can only use the two column references participating in a relationship.
@THENNA_41
Which columns are invloved in the relationship? Is there even a relationship between the two tables?
@tamerj1 that issues has been fixed but sum values are showing different . I have attached Power bi file for your reference sir
Hi @THENNA_41
You already have a Date table, it is better to use it. Please refer to attached file with the proposed solution
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
14 | |
12 | |
10 | |
10 |