Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get filtered date from other table

      I have a table with 2 date fileds financial date and transactional date and 3 cost amounts ,cost value,cost value adjust,cost value phy. The transaction date is in relation with date c...
General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    I created 2 measures as below.

     

    Inventory1 =

    VAR max_date=CALCULATE(MAX('Date'[Date]),ALL('Date'))

    VAR END_DATE=ENDOFMONTH('Date'[Date])

    VAR START_DATE=CALCULATE(min('Date'[Date]),ALL('Date'))

     

    RETURN

    CALCULATE(SUM('1 Inventory Value'[Cost Amount])+SUM('1 Inventory Value'[Cost Amount Adjustment]),FILTER('1 Inventory Value',('1 Inventory Value'[Financial Date]<=END_DATE && '1 Inventory Value'[Financial Date]>=START_DATE) && '1 Inventory Value'[Financial Date]<>DATE(1900,01,01)&& '1 Inventory Value'[Posting Type]

    in{"Physical","Financial"}),ALL('Date'))

    +

     

    CALCULATE(SUM('1 Inventory Value'[Cost Amount Physical]),FILTER('1 Inventory Value',('1 Inventory Value'[Financial Date]<max_date && '1 Inventory Value'[Financial Date]>=START_DATE) &&('1 Inventory Value'[Financial Date]>END_DATE || '1 Inventory Value'[Financial Date]=DATE(1900,01,01))&& '1 Inventory Value'[Posting Type]

    in{"Physical","Financial"}))

     

    Inventory2 =

    CALCULATE (

    [Inventory1],

    ALL ( 'Date'[Date]),

    datesbetween( 'Date'[Date], blank(), lastdate('Date'[Date]))

    )

    And i used Inventory2 and it worked perfectly. Thanks to everyone for giving some hints.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
5 years ago

Anonymous try this measure, change table and column name as per your model

 

Value = 
SUMX ( 
    VALUES ( Cost[Id] ), 
    IF ( CALCULATE ( MAX ( Cost[Tran Date] ) ) <= MAX ( 'Calendar'[Date] ),
    CALCULATE ( SUM ( Cost[Cost Amt] ) ) + CALCULATE ( SUM ( Cost[Cost Adj] ) ),
    CALCULATE ( SUM ( Cost[Cost Phy] ) ) )
)

 

here is the output

 

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Apologies, I have made a little change in the requirement ,i tried the formula for previous requirement but its still running! Please check the new requirement which i updated and suggest.