Group by month per category per location

[Anonymous]

Hi,

How to create a list of months with last known measure for each month per location?

I have a table with measures of water height. We don't have measures for every month, so if it's missing, then we take last month last date.

Location	Water height	Month	Date measured
A	50	1	1.01.2019
A	44	1	15.01.2019
A	60	3	1.03.2019
B	100	1	1.01.2019
B	105	3	1.03.2019
B	110	4	1.04.2019

The desired outcome is:

Month	Location	Last date per month	Water height (last)
1	A	15.01.2019	44
2	A	15.01.2019	44
3	A	1.03.2019	60
4	A	1.03.2019	60
5	A	1.03.2019	60
6	A	1.03.2019	60
7	A	1.03.2019	60
8	A	1.03.2019	60
9	A	1.03.2019	60
10	A	1.03.2019	60
11	A	1.03.2019	60
12	A	1.03.2019	60
1	B	1.01.2019	100
2	B	1.01.2019	100
3	B	1.03.2019	105
4	B	1.04.2019	110
5	B	1.04.2019	110
6	B	1.04.2019	110
7	B	1.04.2019	110
8	B	1.04.2019	110
9	B	1.04.2019	110
10	B	1.04.2019	110
11	B	1.04.2019	110
12	B	1.04.2019	110

Thank you!

[v-shex-msft · Accepted solution]

HI @Anonymous ,

You need to create a calendar table(without relationship), then you can use calendar date and original table location to create a table visual and write a measure to lookup water weight.

Measure = 
VAR currLocation =
    SELECTEDVALUE ( Table1[Location] )
VAR currdate =
    MAX ( 'Table'[Date] )
VAR _datelist =
    CALCULATE (
        MAX ( Table1[Date measured] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            [Location] = currLocation
                && FORMAT ( [Date measured], "mm/yyyy" ) = FORMAT ( currdate, "mm/yyyy" )
        )
    )
RETURN
    IF (
        _datelist <> BLANK (),
        CALCULATE (
            MAX ( Table1[Water height] ),
            FILTER (
                ALLSELECTED ( Table1 ),
                [Location] = currLocation
                    && [Date measured] = _datelist
            )
        ),
        CALCULATE (
            MAX ( Table1[Water height] ),
            FILTER (
                ALLSELECTED ( Table1 ),
                [Location] = currLocation
                    && [Date measured] <= currdate
            )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Great! Worked liked expected.

What would this be called in technical terms? So that I know what to look for and learn more on this topic.

Thank you again!