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?
Share some data to work with. If possible, please ensure that the headings of the table are in English.
The formula that give me the active products:
Should a blank in the end date column be treated as Today's date?
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.
Maybe today plus 30 days.
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.
I can't download the file, but I recently find a way to solve the problem.
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 )
The formula doesn't work:
"the first argument to "DATEADD" must specify a column."
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 )
I tried a different approach. Not sure if it would help
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.
This might help to some extent I think
Unfortunately, it doesn't help. thanks anyway 👍
Find out who won the T-Shirt Design Challenge and the top 3 finalists.
Find out more about the March 2023 update.