Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
THENNA_41
Post Partisan
Post Partisan

Selected Week from Next 4 week value calculation .

I have table name called Invenoty sheet . this table look like . based on the  wknum selection will show rolling average to next 4 weeks .

 

 (Wknum is text format)
 
 

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 

Mesaure  = CALCULATE(sum(Append1[Value]),FILTER(Append1,Append1[Attributes]="Demand"),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),1,MONTH))  

 

 

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 .

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

1.png

View solution in original post

12 REPLIES 12
THENNA_41
Post Partisan
Post Partisan

@tamerj1  its working perfectly . one more questions how to convert above measure SUM to average ?

@THENNA_41 
Just Change SUM to AVERAGE

@tamerj1  I have tired but avg not matching sir 

@THENNA_41 
Please check the updated sample file.

1.png

 

THENNA_41
Post Partisan
Post Partisan

@tamerj1  its working Now perfectly .  I have one more question How to convert above measure  SUM to average ?

tamerj1
Super User
Super User

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 )
)

 

 

 

 

@tamerj1   Not a column from Date. it is a text column(weeknum) in the table.

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_0-1671181598383.png

 

@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 

 

Power bi File  

Hi @THENNA_41 
You already have a Date table, it is better to use it. Please refer to attached file with the proposed solution

1.png

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors