Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

missing time series data part 3

Hi Experts

 

This is a follow on question from the link as shown: https://community.powerbi.com/t5/Desktop/Missing-data-in-time-series-part-2/m-p/682008#M328526

 

Firstly thanks for the excellent feedback. I just have one question. in the table with the observations. if i had multiple entries in this table for X sales throughout the month and i wanted to group the sales for the period in question, so 12 sales in Jan 19 but show the final result in a second column as 12 forJan19 - in sense i am looking to consolidate / group my data into a much smaller table.

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous ,

     

    Is my suggestion helpful to your scenario? If so, would you please accept it as an answer?

     

    Regards,

    Yuliana Gu

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

     

    I tested with below sample table.

     

    New a calculated table, and change the date format to "MMMM yyyy"

    result table =
ADDCOLUMNS (
    FILTER (
        CALENDAR ( MIN ( Sample1[Time] ), MAX ( Sample1[Time] ) ),
        DAY ( [Date] ) = 1
    ),
    "observations", CALCULATE (
        SUM ( Sample1[Observations] ),
        FILTER (
            Sample1,
            Sample1[Time].[Year] = YEAR ( EARLIER ( [Date] ) )
                && Sample1[Time].[MonthNo] = MONTH ( EARLIER ( [Date] ) )
        )
    ) + 0
)

     

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Y GU....

      thanks for the excellent feedback. let me test. much appreicated.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous ,

         

        Is my suggestion helpful to your scenario? If so, would you please accept it as an answer?

         

        Regards,

        Yuliana Gu