DAX for percentage of day from a week

[DebbieE]

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 

[amitchandak]

@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

[DebbieE]

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

[amitchandak]

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

[DebbieE]

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 

Order Quantity by Week % =

Var varOrderQtyWeek = CALCULATE(SUM(FactResellerSales[Order Quantity]), FILTER(ALL(DimDate), DimDate[Year Week]))

RETURN DIVIDE(SUM(FactResellerSales[Order Quantity]), varOrderQtyWeek)

it seems to be doing trick and is based on the above info

[v-jingzhang]

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.

[DebbieE]

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