Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to determine wether a license is active or expired based on License Start Date, License End Date and a Reference Date (can be today or any other date).
Here is the logic for an Active License:
VAR LicenseActiveLogic =
FIRSTDATE ( LineItem[Start Date] ) < LASTDATE ( ReferenceDAte[Date] )
&& LASTDATE ( LineItem[End Date] ) > LASTDATE ( ReferenceDAte[Date] )
I have four tables:
Product (product name),
Opportunity (opportunity name),
LineItem (start date, end date, product name)
and RefereceDate (the reference date).
I am trying to make a measure (since column cannot dynamically calculate with a new reference date) that shows me, for each product in my visual, something like this:
_Active License =
VAR LicenseActiveLogic =
FIRSTDATE ( LineItem[Start Date] ) < LASTDATE ( ReferenceDAte[Date] )
&& LASTDATE ( LineItem[End Date] ) > LASTDATE ( ReferenceDAte[Date] )
RETURN
IF ( LicenseActiveLogic,
TRUE (),
FALSE ()
)
the above returns a weird cartesian product.
I tried the below, but the Active License count is wrong.. I don't know what is happening:
_Active License 2 =
VAR LicenseActiveLogic =
FIRSTDATE ( LineItem[Start Date] ) < LASTDATE ( ReferenceDAte[Date] )
&& LASTDATE ( LineItem[End Date] ) > LASTDATE ( ReferenceDAte[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Product[Prod Name] ),
KEEPFILTERS (
FILTER (
CROSSJOIN ( ReferenceDAte, LineItem, 'Product', Opportunity ),
LicenseActiveLogic
)
)
)
this one shows me only the active licenses and counts wrong.
Ideally, I want to see something like this in my visual:
Reference Date: 1 September 2022 | ||||
Oppty Name | Prod Name | Start Date | End Date | License Active |
Opportunity 5 | Product 5 | 9/15/2022 0:00 | 9/15/2023 0:00 | FALSE |
Opportunity 4 | Product 2 | 4/5/2021 0:00 | 1/5/2023 0:00 | TRUE |
Opportunity 4 | Product 3 | 11/12/2021 0:00 | 1/5/2023 0:00 | TRUE |
Opportunity 4 | Product 3 | 1/5/2022 0:00 | 1/5/2023 0:00 | TRUE |
Opportunity 4 | Product 5 | 1/5/2022 0:00 | 1/5/2023 0:00 | TRUE |
Opportunity 3 | Product 5 | 12/12/2020 0:00 | 1/1/2022 0:00 | FALSE |
Opportunity 3 | Product 5 | 1/1/2021 0:00 | 1/1/2022 0:00 | FALSE |
Opportunity 2 | Product 2 | 2/2/2020 0:00 | 6/7/2021 0:00 | FALSE |
Opportunity 2 | Product 2 | 6/7/2020 0:00 | 6/7/2021 0:00 | FALSE |
Opportunity 2 | Product 3 | 3/4/2020 0:00 | 6/7/2021 0:00 | FALSE |
Opportunity 1 | Product 1 | 1/1/2019 0:00 | 1/1/2020 0:00 | FALSE |
If I change the reference date to any other, it will correctly display TRUE/FALSE based on my license condition.
Can this be done?
I am attaching the PBIX file here
PLEASE HELP
Solved! Go to Solution.
Hi @ChPetru
Solution attached.
A hint:
The dimension for products is not set correctly. This is not the way you normally set a dimension (which poses some problems right now inside PBI). Many id's have been assigned the same product name. You should have a one-to-one correspondence between the product id and the product name. There are too many reasons why this should be so, therefore I'm not going to enlarge upon this.
Nevertheless, the solution works, but please stick to proper data modeling techniques if you want to be safe in the future. Thanks.
Hi @ChPetru
Solution attached.
A hint:
The dimension for products is not set correctly. This is not the way you normally set a dimension (which poses some problems right now inside PBI). Many id's have been assigned the same product name. You should have a one-to-one correspondence between the product id and the product name. There are too many reasons why this should be so, therefore I'm not going to enlarge upon this.
Nevertheless, the solution works, but please stick to proper data modeling techniques if you want to be safe in the future. Thanks.
Hello @daXtreme , thank you for the solution! I was on holiday but just recently tried it; it works if I have a single row in the LineItem table, but if my visual aggregates multiple LineItem rows, measure won't work. How can I work around this isuse and have this measure behave like a calculated column? I will be needing this measure to calculate other measures based on the calculated License State.
Should I use Parameters and try calculated column in Power Query?
How can you aggregate active licences? There's no way to do it unless in some twisted and illogical way... A licence can be active or not at a point in time. When you say "aggregate," what do you exactly mean? You are not asking for the number of active licences but for the state of a licence. What would then be the cumulative state of several licences? There's no good way to define it in terms of "True/False."
You said:
and this is exactly what my solution does.
The table above is just an example of the most granular level we can explore with this data, however, let's say we remove Opportunity Name from the visual. The highest aggregation will be product. Product 3 has both active and inactive licenses, so it will display <blank> in the License State (which is fine if there is conflict between active and inactive license), but if License State is only Active or only Inactive, i will still have blank, which is not ok.
Also, if I want to create a measure that considers License State, will the data that has no License State in my visual be excluded? E.G. I want to calculate revenu for the Active Products; will it exclude any data that has Inactive or Blank Status? If yes, there's a problem.
I still feel the answer to my problem is a calculated column, but not sure how I can calculate the License State using a date parameter, and how I can easily edit the parameter value in the reporting view (not Power Query).
Thank you so much for trying to help me
IF you want to calculate something only for products with all licences active, then it's easy. You just find those products among all those visible in the current context and aggregate over them. That's easy. But the original question was not about aggregation. It was about showing the status (True/False) for the right granularity. That's a completely different request.
IF you want to calculate something only for products with all licences active, then it's easy. You just find those products among all those visible in the current context and aggregate over them. That's easy. But the original question was not about aggregation. It was about showing the status (True/False) for the right granularity. That's a completely different request.
User | Count |
---|---|
43 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |