Forum Discussion

jamuka's avatar
jamuka
Icon for Helper IV rankHelper IV
4 years ago
Solved

How to duplicate data on day level to week level

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.

 

MATERIALForecast MonthForecast Quantity
909941.11.20211.246
909941.12.20211.287
470341.11.20214.179
470341.12.20214.476

 

regards

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

6 Replies

    • jamuka's avatar
      jamuka
      Icon for Helper IV rankHelper IV
      4 years ago

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.