Show last 12 month filtering by month and year

[bdpr_95]

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

[Ashish_Mathur]

Hi,

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

[bdpr_95]

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

            )

)

[Ashish_Mathur]

Hi,

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

[bdpr_95]

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

[Ashish_Mathur]

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.

[bdpr_95]

Maybe today plus 30 days. 

[Ashish_Mathur]

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.

[bdpr_95]

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

[Padycosmos]

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

[bdpr_95]

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

[Padycosmos]

Could you try this please

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

[bdpr_95]

same issue.

[Padycosmos]

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

[bdpr_95]

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.

[Padycosmos]

This might help to some extent I think

https://www.youtube.com/watch?v=nUhU-tXftuM&list=PLApPcvU5-R26t4dylOnPy8A2-lr4C0C--&index=6

[bdpr_95]

Unfortunately, it doesn't help. thanks anyway 👍