cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Show last 12 month filtering by month and year

Hello everyone,

I need to filter by month and year and visualize the last 12 months for my active products.

For example, I filter by January 2023 and analyze data between February 2022 to January 2023.

My biggest problem is working with 2 dates at the same time (start date and end date). Below you can watch the formula that can produce the result that I need, but this does not resolve the part of visualizing the last 12 months.

Does anyone know how to solve this issue?

VAR reference_date = MAX(dim_date[Date])

VAR result =
CALCULATE (
DISTINCTCOUNT(fac_produtos[id_arpu_conta]),
REMOVEFILTERS(dim_date),
fac_produtos[data_ativacao_conta] <> BLANK(),
fac_produtos[data_ativacao_conta] <= reference_date,
OR(
fac_produtos[data_rescisao_conta] > reference_date,
fac_produtos[data_rescisao_conta] = BLANK()
)
)

RETURN

result
16 REPLIES 16
Super User

Hi,

Share some data to work with.  If possible, please ensure that the headings of the table are in English.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I
 product_id product start_date end_date 1 A 01/01/2022 31/12/2022 2 A 01/02/2022 3 A 04/03/2022 30/09/2022 4 A 04/04/2022 30/06/2022 5 A 05/05/2022 6 B 05/06/2022 30/06/2022 7 B 06/07/2022 30/09/2022 8 B 06/08/2022 9 B 06/09/2022

The formula that give me the active products:

VAR reference_date = MAX(dim_date[Date])

VAR result =
CALCULATE (
DISTINCTCOUNT(fac_produtos[product_id]),
REMOVEFILTERS(dim_date),
fac_produtos[start_date] <> BLANK(),
fac_produtos[start_date] <= reference_date,
OR(
fac_produtos[end_date] > reference_date,
fac_produtos[end_date] = BLANK()
)
)
Super User

Hi,

Should a blank in the end date column be treated as Today's date?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

It's not the best approach because if I filter my date for today the product will appear inactive and it's not true.

Super User

I will need some data in the blank cells in that column and it should not be a date way into the future.  The reason is that i intend to create 1 row for each month between the start and end date.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Maybe today plus 30 days.

Super User

Hi,

You may download my PBI file from here.  As of now, when you select 2023 and Jan, you will see data from Feb 2022 to Dec 2022 (not till Jan 2023).  When you open this file tomorrow, you will see till Jan 2023.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

I can't download the file, but I recently find a way to solve the problem.
thanks 👍

Solution Sage

You may try this:

VAR reference_date = MAX(dim_date[Date])

VAR start_date = DATEADD(reference_date, -12, MONTH)

VAR result = CALCULATE ( DISTINCTCOUNT(fac_produtos[id_arpu_conta]), REMOVEFILTERS(dim_date), fac_produtos[data_ativacao_conta] <> BLANK(), fac_produtos[data_ativacao_conta] <= reference_date,

OR( fac_produtos[data_rescisao_conta] > reference_date, fac_produtos[data_rescisao_conta] = BLANK() ), dim_date[Date] >= start_date )

RETURN

result

Helper I

The formula doesn't work:
"the first argument to "DATEADD" must specify a column."

Solution Sage

VAR start_date = DATEADD(MAX(dim_date[Date]), -12, MONTH)

VAR result = CALCULATE ( DISTINCTCOUNT(fac_produtos[id_arpu_conta]), REMOVEFILTERS(dim_date), fac_produtos[data_ativacao_conta] <> BLANK(), fac_produtos[data_ativacao_conta] <= MAX(dim_date[Date]),

OR( fac_produtos[data_rescisao_conta] > MAX(dim_date[Date]),, fac_produtos[data_rescisao_conta] = BLANK() ), dim_date[Date] >= start_date )

RETURN

result

Helper I

same issue.

Solution Sage

I tried a different approach. Not sure if it would help

Helper I

No, what I need is something like this video: https://www.youtube.com/watch?v=d8Rm7dwM6gc

But here Alberto Ferrari works with just one date, in my case, I need to work with the start date and end date to find out my active products by a month.

Solution Sage
Helper I

Unfortunately, it doesn't help. thanks anyway 👍

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors