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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
solankikumar
New Member

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

 

solankikumar_0-1713402145471.png

 

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

 

solankikumar_1-1713402170017.png

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 

1 REPLY 1
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.