Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
expected result
Hello,
I have a product table that looks like that:
Product Id | Product Name |
1 | Product Y |
2 | Product X |
3 | Product Z |
And I have a Subscription Product Status table that a product can be in Status "Active" or "Not Active".
Table Id (Unique) | Product Id | Status | Status Date |
1 | 1 | Active | 20/01/2018 |
2 | 1 | Not Active | 23/01/2018 |
3 | 2 | Active | 28/01/2018 |
4 | 3 | Active | 14/05/2018 |
5 | 1 | Active | 06/03/2019 |
6 | 1 | Not Active | 12/08/2019 |
7 | 3 | Not Active | 24/02/2020 |
8 | 1 | Active | 06/06/2020 |
9 | 2 | Not Active | 22/12/2021 |
10 | 3 | Active | 02/03/2022 |
As you can see this Subscription Status Table holds the historical status of every product, and every row represents a status change.
My goal is eventually to create a visual that shows Every month the number of Active products that I had.
I need a formula to create a measure to achieve this goal.
Thank you
Solved! Go to Solution.
I have updated the solution as requested to only check if the product was still active at the end of the month. (Originally you implied you wanted active anytime of the month)
Click here to download a solution
Now please click thumbs up and accept as solution.
It is not fair to keep changing to problem description.
If we the fix the problem then please accept the solution and raise a new ticket if you want to change the problem description.
Hi @xl0911
I don't understand what is the problem. Here is your sample file with the very same code
@xl0911 I have accepted my solution because I proivided 2 solutions that worked but you keep changing the problem description.
In future when you raise a problem, please show your thanks to volunteers who help with a thumb click and accept the solution. One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
Hi @xl0911
This version is working perfectly in the sample file. Please download the sample file and compare with yours. https://we.tl/t-c14Wj8tpax
# Active Subscriptions =
SUMX (
VALUES ( Products[Product Id] ),
CALCULATE (
VAR PeriodEnadDate =
MAX ( 'Date'[Date] )
VAR TableBeforePeriodEnd =
FILTER ( Subscription, Subscription[Status Date] <= PeriodEnadDate )
VAR PreviousActiveTable =
FILTER ( TableBeforePeriodEnd, Subscription[Status] = "Active" )
VAR LastActivation =
COALESCE ( MAXX ( PreviousActiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
VAR PreviousIactctiveTable =
FILTER ( TableBeforePeriodEnd, Subscription[Status] <> "Active" && Subscription[Status Date] > LastActivation )
VAR LastInactivation =
COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
VAR Result =
IF (
PeriodEnadDate >= LastActivation && PeriodEnadDate <= LastInactivation,
1
)
RETURN
COALESCE ( Result, 0 ),
CROSSFILTER ( 'Date'[Date], Subscription[Status Date], None )
)
)
Hi,
You may download my PBI file from here.
Hope this helps.
I think your solution is not what I meant because Product Id 1 is not active in the end of the month so under product Id 1 in jan-2018 the count should be 0.
Hi @xl0911
Here is the updated solution with active relationship with the date table and fixed the bug noticed by @speedramps
# Active Subscriptions =
SUMX (
VALUES ( Products[Product Id] ),
CALCULATE (
VAR PeriodStartDate =
MIN ( 'Date'[Date] )
VAR PeriodEnadDate =
MAX ( 'Date'[Date] )
VAR TableBeforePeriodEnd =
FILTER ( Subscription, Subscription[Status Date] <= PeriodEnadDate )
VAR PreviousActiveTable =
FILTER ( TableBeforePeriodEnd, Subscription[Status] = "Active" )
VAR LastActivation =
COALESCE ( MAXX ( PreviousActiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
VAR TableBeforePeriodStart =
FILTER ( Subscription, Subscription[Status Date] < PeriodStartDate )
VAR PreviousIactctiveTable =
FILTER ( TableBeforePeriodStart, Subscription[Status] <> "Active" )
VAR LastInactivation =
COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
VAR Result =
IF (
PeriodEnadDate >= LastActivation && PeriodStartDate <= LastInactivation,
1
)
RETURN
Result,
CROSSFILTER ( 'Date'[Date], Subscription[Status Date], None )
)
)
I wanted to upload more screenshots but seems I reached my maximum allowable limit of uploaded images!
Thank you.
I'm getting this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
I guess that maybe the formula can't handle a scenario that maybe a product has 2 rows of status in the same date... ?
Found my Mistak..
@xl0911
Please check the latest version of the code and the sample file as I amended as per your reply to @speedramps
I checked your file and it's not the result I wanted.
I attached a screenshot of the result I want.
Why is that? Jan should be 1 right as ID 2 is active. What exactly is your business logic?
Product ID 1 at Jan 2018 should be 0 because in the end this month the product wat "Not Active".
Product ID 1 at March 2019 return to be 1 until the product was "Not Active" at August 2019.
Hope it make sense..
@xl0911
That is exactly what the code is doing. But also Product ID2 is active in Jan therefore it is counted this is why you see 1
This is a screenshot of your attached file, as you can see you have 2 at Jan 2018
@xl0911
This is not the last file. I shared one more solution after that. Here you go https://we.tl/t-17UszDTihZ
# Active Subscriptions =
SUMX (
VALUES ( Products[Product Id] ),
CALCULATE (
VAR PeriodEnadDate =
MAX ( 'Date'[Date] )
VAR TableBeforePeriodEnd =
FILTER ( Subscription, Subscription[Status Date] <= PeriodEnadDate )
VAR PreviousActiveTable =
FILTER ( TableBeforePeriodEnd, Subscription[Status] = "Active" )
VAR LastActivation =
COALESCE ( MAXX ( PreviousActiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
VAR PreviousIactctiveTable =
FILTER ( TableBeforePeriodEnd, Subscription[Status] <> "Active" && Subscription[Status Date] > LastActivation )
VAR LastInactivation =
COALESCE ( MAXX ( PreviousIactctiveTable, Subscription[Status Date] ), DATE ( 3000, 1, 1 ) )
VAR Result =
IF (
PeriodEnadDate >= LastActivation && PeriodEnadDate <= LastInactivation,
1
)
RETURN
COALESCE ( Result, 0 ),
CROSSFILTER ( 'Date'[Date], Subscription[Status Date], None )
)
)
Your solution is perfect BUT its not what I need.
Unfortunately I did a big mistake in my simulation and I need to add one more table (you see, any customer can subscribe to any product ID so I miss the sales table between the product table and the status history table).
Now I don't know what to do, should I open a new question or continue from here, what do you suggest ?
thanks for not giving up 🙂
@xl0911
Thats fine. Please add all relevant information and sample data including the relevant part of your data model and relastionships
Hi @xl0911
I don't understand what is the problem. Here is your sample file with the very same code
I found A small problem in your solution.
If there is the same date with the same Product Id more then one time its not "tie breaker" with the largest ID.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |