Forum Discussion

Varishtha's avatar
Varishtha
Icon for Helper I rankHelper I
2 years ago
Solved

Stagger Chat in Power BI

I am trying to make a stagger chart for actual sales generated and forecasted sales for next 4 months.
Below is what it looks like in excel:

 



I tried to put "sales" measure in the matrix, but I don't know why am getting 0 for some dates:


I have used start_of_month in row header and month in column header. Below is the measure I have used above:

exp v_BKsales1 = var x = IF( 
                             MONTH( STARTOFMONTH( Calender[Start of Month] ) )=MONTH( SUM(Calender[_Month] ) ), 
                             [UMSATZ_BK], 0 )
Here, [UMSATZ_BK] is Sum of order sales.



++
I tried to find the cause. When I made a bit modification to the measure, I saw the result below:
exp v_BKsales1 = IF(
                    MONTH(STARTOFMONTH(Calender[Start of Month]))=MONTH( SUM(Calender[_Month] )),
                    MONTH( SUM(Calender[_Month] )), 0)


 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Varishtha ,

    If you want to use 20 as your forecast sales value for the next month, please take a look at this test:

    My Model View:

    Calender = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"Start of Month",DATE(2024,MONTH([Date]),1),"_Month",MONTH([Date]))

    I created another new table:

    Table 2 = VALUES('Calender'[Start of Month])

    My Report View and Measure value:

    Measure = IF(

     MONTH( MAX( 'Calender'[Start of Month] ) )=MAX('Calender'[_Month] ) ,

    MAX('Calender'[_Month]), 0)

    Measure 3 = IF( MONTH( MAX('Calender'[Start of Month]))  = MONTH( MAX('Table 2'[Start of Month]) ) ,MONTH( MAX('Calender'[Start of Month])),  IF( MONTH( MAX('Calender'[Start of Month])) +1 = MONTH( MAX('Table 2'[Start of Month]) ) , 20 , BLANK()))

    Best Regards,

    Sunshine Gu 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Varishtha ,

    Could you please replace the STARTOFMONTH function outside Callender [Start of Month] and the MONTH function outside Callender [_Month] with the Max function in the measure?

    I did a test for your reference.

    In my scenario:

    Since you didn't provide a pbix file, first I assume a table, as shown in the image below:

    Here is a screenshot of my metrics and matrices in the Report View:

     

    Measure = IF(

                                 MONTH( MAX( Calender[Start of Month] ) )=MAX(Calender[_Month] ) ,

                                 MAX(Calender[_Month]), 0)

     

    If the problem exists,please provide me with some sample data and the results based on the sample data. That would be very helpful. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,

    Sunshine Gu

    • Varishtha's avatar
      Varishtha
      Icon for Helper I rankHelper I
      2 years ago

      Hello  ,
      Thanks a lot, I did figure this out. Now I wanted to move ahead with my original task. I want to add forecasted sales (that will be predicted in each month for the next four months). Below snap shows how it looks in excel:

       

      So I modified my measure as below:

      exp v_BKsales1 = IF(

                         (MONTH(MAX(Calender[Start of Month]))+1)=MONTH(MAX(Calender[_Start_of_month])),

                         20,

                         IF( MONTH(MAX(Calender[Start of Month]))=MONTH(MAX(Calender[_Start_of_month])),

                         [UMSATZ_BK],

                           0)

      )

      Note that I have used 20 inplace of forecasting measure for simplicty sake.
      As per the modified measure I should have been able to see 20 in each row next to sales. But it is not working:

       

       

      It would be really helpful if you can help me sort this out.

      Regards,
      Varishtha N

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Varishtha ,

        If you want to use 20 as your forecast sales value for the next month, please take a look at this test:

        My Model View:

        Calender = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"Start of Month",DATE(2024,MONTH([Date]),1),"_Month",MONTH([Date]))

        I created another new table:

        Table 2 = VALUES('Calender'[Start of Month])

        My Report View and Measure value:

        Measure = IF(

         MONTH( MAX( 'Calender'[Start of Month] ) )=MAX('Calender'[_Month] ) ,

        MAX('Calender'[_Month]), 0)

        Measure 3 = IF( MONTH( MAX('Calender'[Start of Month]))  = MONTH( MAX('Table 2'[Start of Month]) ) ,MONTH( MAX('Calender'[Start of Month])),  IF( MONTH( MAX('Calender'[Start of Month])) +1 = MONTH( MAX('Table 2'[Start of Month]) ) , 20 , BLANK()))

        Best Regards,

        Sunshine Gu 

  • Varishtha's avatar
    Varishtha
    Icon for Helper I rankHelper I
    2 years ago

    Hello Anonymous ,
    Thanks a lot, I did figure this out. I want to add forecast sales for next 4 months as well (like in the excel snap below).

    So, I modified the measure accordingly, but I am getting empty values in the table(as visble in snap below):
    Measure:

    exp v_BKsales1 = IF(
                       (MONTH(MAX(Calender[Start of Month]))+1)=MONTH(MAX(Calender[_Start_of_month])),
                       20,
                       IF( MONTH(MAX(Calender[Start of Month]))=MONTH(MAX(Calender[_Start_of_month])),
                       [UMSATZ_BK],
                         0)
    )

    P.S., I have mentioned "20" , instead of the sales measure, just for simplicity sake.

    The number 20 should have populated in the blocks I have marked in snap above, but no values are coming.

    It would be really helpful if you could help me with this.

    Regards,
    Varishtha N