cancel
Showing results for
Did you mean:
Highlighted
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.

 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!

1 ACCEPTED SOLUTION

Accepted Solutions
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
)
)
)
```

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

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

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: | |
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!

Announcements

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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 425 members 5,011 guests
Recent signins: