reference
2 TopicsDate 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 HELPSolved1.4KViews0likes6Comments
Reference a row in another column
Hello, I hve two columns in the same table, onde of them is the index one, I want to reference the id number of that index, for example: ID INDEX 8027890 1 9809887 2 2938878 3 What I need is to find out if an ID was entered twice, so I need If the ((ID of index 1)-(ID of index -1))=0, "SAME ID", "DIFFERENT ID" How do I make this reference tho? Thank a lot!!Solved1.2KViews0likes2Comments
