How to duplicate data on day level to week level

[jamuka]

Hello all,

I'd like show my montly forecast on a weekly matrix. below you can see my current matrix. What I want is I want to show W44 Data on W45, W46 and W47. I'm not sure whether this is possible in dax or I should duplicate my forecast table to show it on week level.

My Forecast Data, it shows my forecast on month level, I wrote day on Forecast Month Column so I can create a relationship with my Date Table.

MATERIAL	Forecast Month	Forecast Quantity
90994	1.11.2021	1.246
90994	1.12.2021	1.287
47034	1.11.2021	4.179
47034	1.12.2021	4.476

regards

[amitchandak]

@jamuka , refer if the blog on a similar topic can help

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

[jamuka]

Hello @amitchandak,

thank you for your response. sorry for misunderstanding I don't want to split my value on week level I want to show same value for each week.

But I tried to use your code to replicate "Daily Target" value to learn how it works but it didn't return a value.

I created Month Date as 

Forecast Date = EOMONTH(Forecast[Forecast Month],0)

and Daily Target as

Daily Forecast =

CLOSINGBALANCEMONTH (

SUMx ( Forecast,Forecast[Forecast Quantity] )  /

(MAXX (Forecast,DATEDIFF ( Forecast[Forecast Month], Forecast[Forecast Date], DAY ) ) + 1

), 'Takvim'[Date])

regards

[v-rzhou-msft · Accepted solution]

Hi @jamuka ,

I think your problem should be caused by your relationship between your Date table and Fact Data table. You only have forecast data in the first day of a month. So you could only see forecast values in Week contains these dates.

Here I suggest you to inactive the relationship and create a measure to calcualte Forecast Quantity.

Other values which are calculated by relationship, you can try to create measures by USERELATIONSHIP function.

Date column: 

Date = ADDCOLUMNS( CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"Wk","WK"&""&WEEKNUM([Date]))

Measure:

Forecast =
VAR _ADD =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Year", YEAR ( 'Table'[Forecast Month] ),
        "Month", MONTH ( 'Table'[Forecast Month] )
    )
VAR _SUM =
    SUMMARIZE ( _ADD, [MATERIAL], [Year], [Month], [Forecast Quantity] )
VAR _GENERATE =
    GENERATE (
        VALUES ( 'Table'[MATERIAL] ),
        SUMMARIZE ( 'Date', 'Date'[Year], 'Date'[Month], 'Date'[Wk] )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _GENERATE,
        "Forecast",
            SUMX (
                FILTER (
                    _SUM,
                    [MATERIAL] = EARLIER ( [MATERIAL] )
                        && [Year] = EARLIER ( [Year] )
                        && [Month] = EARLIER ( [Month] )
                ),
                [Forecast Quantity]
            )
    )
RETURN
    SUMX (
        FILTER (
            _ADD2,
            [MATERIAL] = MAX ( 'Table'[MATERIAL] )
                && [Month] = MAX ( 'Date'[Month] )
        ),
        [Forecast]
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jamuka]

Hello @v-rzhou-msft,

thank you it works. But in your file relation is between Date and Forecast Quantity. Shouldn't it be between Date and Forecast Month? 

or it doesn't matter because it is an inactive relationship?

regards

[v-rzhou-msft]

Hi @jamuka ,

Yes, in my sample, I made a mistake in relationship. As you mentioned the relationship should be between [Date] and [Forecast Month].  It doesn't matter because it is an inactive relationship. Your issue is caused by active relationship between [Date] and [Forecast Month]. Due to the relationship, your will only show WK45(2021/11/01) and WK49(2021/12/01). If we inactive all relationships between two tables, you can get result as you want by measure.

As my above reply, I suggest you to inactive the relationship and create a measure to calcualte Forecast Quantity.

Other values which are calculated by relationship, you can try to create measures by USERELATIONSHIP function.

Could you kindly Accept my reply as a solution if my reply help you solve your problem?

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jamuka]

Hello @v-rzhou-msft,

I thougt inactive relationship should be between related fields (e.g date to date) thanks for information.

I did mistakenly accept my answer as solution sorry for that 🙂