Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Hi,
Share some data to work with. If possible, please ensure that the headings of the table are in English.
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:
Hi,
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.
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.
I can't download the file, but I recently find a way to solve the problem.
thanks 👍
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."
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.
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
https://www.youtube.com/watch?v=nUhU-tXftuM&list=PLApPcvU5-R26t4dylOnPy8A2-lr4C0C--&index=6
Unfortunately, it doesn't help. thanks anyway 👍
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |