Forum Discussion

solankikumar's avatar
solankikumar
New Member
2 years ago

Using change log to create weekly fact data

Hi Experts , 

I have change log table with effective date for No. of packs in Box we sell (sample below) - 3 years data

 

 

- & i have data for No. of packs as of today (for all material) - like below 

 

what would be best calc to use above which can give me what was EA in Box for all week ending in last 3 years. 

 

Thanks 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    solankikumar , Have date table joined with Changed Date. Have a week column in that

     

    example

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

     

     

    Then have measures like

    calculate(lastnonblankvalue(Date[Date], Sum(Table[New Value])), filter(all(Date), Date[Date] <= Max(date[Date]) ) )

     

    Use this along with Week start/end date from date table and other from table