cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SurferMike Frequent Visitor
Frequent Visitor

Group by month per category per location

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.

LocationWater heightMonthDate measured
A5011.01.2019
A44115.01.2019
A6031.03.2019
B10011.01.2019
B10531.03.2019
B11041.04.2019

 

The desired outcome is:

MonthLocationLast date per monthWater height (last)
1A15.01.201944
2A15.01.201944
3A1.03.201960
4A1.03.201960
5A1.03.201960
6A1.03.201960
7A1.03.201960
8A1.03.201960
9A1.03.201960
10A1.03.201960
11A1.03.201960
12A1.03.201960
1B1.01.2019100
2B1.01.2019100
3B1.03.2019105
4B1.04.2019110
5B1.04.2019110
6B1.04.2019110
7B1.04.2019110
8B1.04.2019110
9B1.04.2019110
10B1.04.2019110
11B1.04.2019110
12B1.04.2019110

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Group by month per category per location

HI @SurferMike ,

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
            )
        )
    )

2.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team
Community Support Team

Re: Group by month per category per location

HI @SurferMike ,

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
            )
        )
    )

2.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
SurferMike Frequent Visitor
Frequent Visitor

Re: Group by month per category per location

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!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 425 members 5,011 guests
Please welcome our newest community members: