Forum Discussion
Date Reference as parameter to determine True/False
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
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.
6 Replies
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.
