DAX to change monthly figures in to Weekly? Need the results in a table

[Anonymous]

Hi all

I have been given a quarterly budget with monthly values that I need to change in to weekly, in order to compare against our weekly actual spend.

The data looks like so:

Team	November	December	January	Febuary
team 1	100	120	140	90

I have unpivoted in query editor to make this:

Team	Month	Value
team 1	November	100
team 1	December	120

To make a weekly budget I just want to take the monthly figure and divide by 4 - assuming straight line usage across the month.

End result should be something like this:

Team	Week End	Values
Team 1	6/11/2020	25
Team 1	13/11/2020	25
Team 1	20/11/2020	25
Team 1	27/11/2020	25

Can anyone help with some dax / query editor tips?

[AlB · Accepted solution]

Hi @Anonymous 

This can also be done in Power Query but here is a DAX solution.

You need a date table with the End of Week days and to specify the year in your initial table. You can then create  a new calculated table. See it all at work in the attached file.

Table2 = 
SELECTCOLUMNS (
    GENERATE (
        Table1,
        VAR auxT_ =
            CALCULATETABLE (
                DISTINCT ( DateT[End of Week] ),
                FILTER (
                    ALL ( DateT[Year], DateT[Month Name], DateT[Date], DateT[End of Week] ),
                    DateT[Year] = Table1[Year] && DateT[Month Name] = Table1[Month] && DateT[Date] = DateT[End of Week]
                )
            )
        RETURN
            ADDCOLUMNS ( auxT_, "res", DIVIDE ( Table1[Value], COUNTROWS ( auxT_ ) ) )
    ),
    "Team", [Team],
    "Week end", [End of Week],
    "Values", [res]
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@Anonymous ,Not excat, but this atricle on similar topic, you need to have week of month if needed

#powerbi Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290