Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bdpr_95
Helper I
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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

product_idproductstart_dateend_date
1A01/01/202231/12/2022
2A01/02/2022 
3A04/03/202230/09/2022
4A04/04/202230/06/2022
5A05/05/2022 
6B05/06/202230/06/2022
7B06/07/202230/09/2022
8B06/08/2022 
9B06/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()
            )
)

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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
https://www.linkedin.com/in/excelenthusiasts/

Maybe today plus 30 days. 

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.

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Padycosmos
Solution Sage
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

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

bdpr_95_0-1674842037838.png

 

 

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

same issue.

 

bdpr_95_0-1674842984567.png

 

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

Padycosmos_0-1674844086840.png

 

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.

Unfortunately, it doesn't help. thanks anyway 👍

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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