Forum Discussion

fab196's avatar
fab196
Helper II
4 years ago
Solved

dax date parameter

hi everyone, need your help i have table having two dates column both are important and i want use that id  loc ack date ship date 1 mumbai 8/10/2021 9/10/2021 1 guj 11/10/2021 1...
Need Help
Show and Tell
Tips and Tricks
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi fab196 ,

     

    According to your description, a separate date column is created without creating a relationship between the tables. The measure created is used to count the number of locs under the selected date.

    I did a test reference as follows:

    M =
VAR sel_data =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ack date] = sel_data )


    If the problem is still not resolved, please provide more information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k's avatar
parry2k
Super User
4 years ago

fab196 create a date table following my post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

 

after the table is added, set the relationship date table on both the dates and one relationship will be inactive.

 

Add following two measures assuming an inactive relationship with a ship date

 

Count = COUNTROWS ( YourTable )

Ship Count = CALCULATE ( [Count], USERELATIONSHIP ( DateTable[Date], YourTable[ShipDate] ) )

 

Use slicer from new date table and it should work.

 

✨ Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡

 

  • fab196's avatar
    fab196
    Helper II
    4 years ago

    Hi sir,

    thanks for your valuable reply.your given solution is right but requiremnt is slight different .

    please chek below sql code i want to do similar using dax and my date parameter should be dynamic

     

    please help i m lot of struggling with this issue 

     

    declare @date as date ='2021-9-20'


    select count(id) as ship,0 as ack from tramsaction where ship date >=date and ship date<=date
    union
    select 0 as ship , count(id) as ack from tramsaction where ack date>=cast( datefromparts(year(@strdate),month(@strdate),1) as datetime) and ack date<=CONVERT(date, @strdate, 120) )
    group by location

     

    amitchandak