topic Re: Measure that returns an yearly value for each month in DAX Commands and Tips

Measure that returns an yearly value for each month

fpennisi17 — Tue, 29 Sep 2020 12:41:15 GMT

In my model, I have a table with Year and Year&Month columns.

For some measures in my dataset, I have a DAX measure that tells me im which Year&Month the measure itself has the last valid value.

I want to create a report page in which the user selects a single year and a table with all the months in that year is shown.

For each month, the other column in the table should report the year & month for which a measure has last value, if the year is the same, otherwise the last month of the year.

I will try to clarify with an example:

- I have data from 2018 to 2020

- One measure (Production) has its last value on August 2020 (this value is provided with the measure called "Last production Month"

- The user, in the report page, will select a year:

* if the year is 2020, the table should shown all 2020 months (Jan to Dec) in the first column and the month provided by the "Last Production Month" in the second column (in this case, August 2020)

* if the year is 2018 or 2019, the second column should show, instead, "December 2018" or "December 2019" respectively

Is there a way to achieve this with a DAX measure?

Thank you

Re: Measure that returns an yearly value for each month

Greg_Deckler — Tue, 29 Sep 2020 13:50:52 GMT

fpennisi17 Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Measure that returns an yearly value for each month

fpennisi17 — Wed, 30 Sep 2020 10:32:11 GMT

You are right, I wrote a mess. I will try with an example

This is my "Year & Month"

Month ID	Year
2019-01	2019
2019-02	2019
2019-03	2019
2019-04	2019
2019-05	2019
2019-06	2019
2019-07	2019
2019-08	2019
2019-09	2019
2019-10	2019
2019-11	2019
2019-12	2019
2020-01	2020
2020-02	2020
2020-03	2020
2020-04	2020
2020-05	2020
2020-06	2020
2020-07	2020
2020-08	2020
2020-09	2020
2020-10	2020
2020-11	2020
2020-12	2020

I have several timeseries in the model (with 15 minute or daily granularity).

For each timeseries I created a DAX measure that returns the last Year&Month in which that timeseries registered data (e.g.: for the Production of a site, the last data was registered in September 2020, so the measure returns 2020-09.

If this is the case, it means that I have data in the whole 2019 and in 2020 until september.

So I would like to create a DAX measure (let's call it "Last data"), for the previous timeseries, that returns the following:

If the selected year is 2019, the last data for the timeseries in that year is on December, so:

Month ID	Last data
2019-01	2019-12
2019-02	2019-12
2019-03	2019-12
2019-04	2019-12
2019-05	2019-12
2019-06	2019-12
2019-07	2019-12
2019-08	2019-12
2019-09	2019-12
2019-10	2019-12
2019-11	2019-12
2019-12	2019-12

If the selected year is 2020, the last data for the timeseries in that year is on September, so:

Month ID	Last data
2020-01	2020-09
2020-02	2020-09
2020-03	2020-09
2020-04	2020-09
2020-05	2020-09
2020-06	2020-09
2020-07	2020-09
2020-08	2020-09
2020-09	2020-09
2020-10	2020-09
2020-11	2020-09
2020-12	2020-09

I hope it is clear now.

Is that possible?