Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChPetru
Helper I
Helper I

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 NameProd NameStart DateEnd DateLicense Active
Opportunity 5Product 59/15/2022 0:009/15/2023 0:00FALSE
Opportunity 4Product 24/5/2021 0:001/5/2023 0:00TRUE
Opportunity 4Product 311/12/2021 0:001/5/2023 0:00TRUE
Opportunity 4Product 31/5/2022 0:001/5/2023 0:00TRUE
Opportunity 4Product 51/5/2022 0:001/5/2023 0:00TRUE
Opportunity 3Product 512/12/2020 0:001/1/2022 0:00FALSE
Opportunity 3Product 51/1/2021 0:001/1/2022 0:00FALSE
Opportunity 2Product 22/2/2020 0:006/7/2021 0:00FALSE
Opportunity 2Product 26/7/2020 0:006/7/2021 0:00FALSE
Opportunity 2Product 33/4/2020 0:006/7/2021 0:00FALSE
Opportunity 1Product 11/1/2019 0:001/1/2020 0:00FALSE

 

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

1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

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.

View solution in original post

6 REPLIES 6
daXtreme
Solution Sage
Solution Sage

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?

@ChPetru 

 

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:

daXtreme_1-1664971307520.png

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

@ChPetru 

 

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.

@ChPetru 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors