Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Say you have a date table
And a fact table of Sales
And a dimension of products (Product Type)
Putting this all together is there a measure that can be created that comes up with the percentage of sales that day against the entire week?
Im guessing I need to have a week No in the date table to use in the DAX along with the day name
the results will end up in a Matrix
@DebbieE , Week number, better week rank on year week ,
Then you can week data like
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Day % = divide(sum('Table'[Qty]), [This week])
you need new column
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
I am getting the error on the first Week Rank a single value for column Week Start date cane be determined which I think seems reasonable because in the date dimension the Week Start date is the same for 7 values? I cant seem to get any further with that first option
@DebbieE , Hope you are creating week start date as column in a date table
example
Date = ADDCOLUMNS( CALENDAR(date(2018,01,01), max(Sales[Sales Date]))
, "Month Year", FORMAT([Date], "MMM-YYYY")
, "Month Year Sort" , (year([Date])*100) + MONTH([Date]) ,
"Year" , Year([Date]) , "Week", format(WEEKNUM([Date],2),"\W00")
, "Year Week" , (year([Date])*100) + WEEKNUM([Date],2)
, "Week Day" , WEEKDAY([Date],2)
, "Week Start Date", [Date] - WEEKDAY([Date],2) +1
, "Week Day Name" , Format([Date], "ddd")
)
Yes I am creating Week Start date in the Date table
so there are 7 rows with the same Week Start Date which is why its not working I think.
I also tried with Option 2 on Year Week in the Dim table and this says it cannot be determined. When a measure formula refers to a column that contains many values without specifying an aggregation.
Its really hard to know what to do because I dont know what results i should be getting at the specific points in the process. Should I add a Min round the start of week ? this at least gets the DAX working but every single value is 1?
I have come at it not using the RANK because It didnt work for me. I created a Year Week Field in the Date time table in Power Query. E.g. 202152 and used this
Hi @DebbieE
Using a Year Week Field in the Date table is good. Then you can also use below measure to get the percentage.
Order Quantity by Week % =
VAR varWeekNo = MAX ( DimDate[Year Week] ) // or selectedvalue(DimDate[Year Week])
VAR varOrderQtyDay = SUM ( FactResellerSales[Order Quantity] )
VAR varOrderQtyWeek =
CALCULATE (
SUM ( FactResellerSales[Order Quantity] ),
ALL ( DimDate[Date] ),
DimDate[Year Week] = varWeekNo
)
RETURN
DIVIDE ( varOrderQtyDay, varOrderQtyWeek )
If you add Week Start Date or Week End Date to the Date table, you just need to replace DimDate[Year Week] with DimDate[Week Start Date] in the measure. The result should be same.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
thank you, Although the user has just completely redefined what they now want for this metric so its back to the drawring board possibly
However I did try this and it did this to my matrix which I dont want (Or undersstand to be honest
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |