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
ammar_cs_2004
Frequent Visitor

Determine whether A Date is In-between Two Dates in Power BI

Hi everyone,

I have two tables.The first one is for Asset History The second one is for items Issue for Asset

I want the to do add new column to the second table to bring To Code

 

First Table (Asset Transaction):

Transaction NameAsset CodeStart DateEnd DateTo CodeTo Name
Asset Custodian Opening5930631/12/201718/06/2019S00001EMP 1
Asset Transfer Custodian5930619/06/201924/08/2020S00002EMP 2
Asset Transfer Custodian5930625/08/202011/03/2022S00003EMP 3
Asset Transfer Custodian5930612/03/202203/10/2022S00004EMP 4

 

Second Table (Stock Ledger):

Vr. No.Vr. DateTransaction NameAsset CodeItem CodeItem NameQTYAmountTo Code
10190010521/09/2019Material Issue5930610801010289Printer Ink Cartridge HP # Black 950XL # CN045AN1117 
10190010521/09/2019Material Issue5930610801010292Printer Ink Cartridge HP # 951XL Yellow # CN048AN190 

 

i try this code but not working fine.

 

TO Code =
VAR _occurrenceDate = SELECTEDVALUE('Stock Ledger'[Vr. Date])
VAR _occurrenceCode = SELECTEDVALUE('Stock Ledger'[Asset Code])
RETURN
CALCULATE(
SELECTEDVALUE('Asset Transaction'[To Code]),
FILTER('Asset Transaction',
_occurrenceDate >= 'Asset Transaction'[Start Date] &&
_occurrenceDate <= 'Asset Transaction'[End Date] &&
_occurrenceCode <> BLANK() &&
_occurrenceCode == 'Asset Transaction'[Asset Code]
)
)

 

2 REPLIES 2
ReneMoawad
Resolver II
Resolver II

Hello @ammar_cs_2004,

 

Try the below:

 

To Code =
VAR _occurrenceDate = 'Stock Ledger'[Vr. Date]
VAR _occurrenceCode = 'Stock Ledger'[Asset Code]
RETURN

CALCULATE(
    FIRSTNONBLANK('Asset Transaction'[To Code], 1)

    , _occurrenceCode == 'Asset Transaction'[Asset Code]

    , _occurrenceCode <> BLANK()

    , _occurrenceDate >= 'Asset Transaction'[Start Date]

    && _occurrenceDate <= 'Asset Transaction'[End Date]

)

thanks for reply but it is not working have error 

(The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.)

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.