cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
joshua1990
Post Prodigy
Post Prodigy

Calculation just for finished days

Hello everybody!

I have a measure that shows me the ration between sales and budget:

Ratio = [Sales] / [Budget]

 

How is it possible to get the ratio for the whole week but just until yesterday > just for finished days?

2 REPLIES 2
amitchandak
Super User
Super User

@joshua1990 , You need a date calendar with Week and week day defind

 

Column in date tbale

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2) //monday week -- WEEKNUM([Date],1) //sunday week
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Weekday = WEEKDAY([Date],2)

measure

WTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday])-1))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi



Appreciate your Kudos.

Anonymous
Not applicable

 

Ratio Revised =
var __today = TODAY()
return
CALCULATE(
    [Ratio],
    KEEPFILTERS( Dates[Date] < __today )
)

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors