missing time series data part 3

[Anonymous]

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.

[v-yulgu-msft]

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]

Hi Y GU....

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

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

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

Regards,

Yuliana Gu