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 locack dateship date
1mumbai8/10/20219/10/2021
1guj11/10/202113/10/2021
3mumbai9/10/202113/10/2021
5mumbai11/10/202113/10/2021
5guj11/10/202113/10/2021
6mumbai9/10/202111/10/2021

it will return 

aim is to calculate count of id based on the ack and ship date 

for ex.if user select the date  11/10/2021 then it will return  count of that day group by location 

so count will be 3 overall ..how to i do that using dax

and so how to i create calender table and use the the single date filetr for particuler date using dax

 

please help 

amitchandak 

parry2k 

 

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.

6 Replies

  • 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 

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    fab196 doesn't make sense what you are looking for. why you want the union and set values to zero, it will return two rows, how you will visualize it? What you are trying to do? The answer I provided earlier gives you a count based on the selected date, not sure why you want two rows with 0 values.

     

     

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    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.

    • fab196's avatar
      fab196
      Helper II
      4 years ago

      v-henryk-mstf 

      hi ,

      it is possible to generate code like sql in dax

      here is my sql code

      select sum(ship),sum(ack) from (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)a

       

      please help

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi fab196 ,

     

    The sql code and dax have different calculation logic and cannot be directly converted based on the code you provided.

     

    Does the solution given above meet your requirements, if there are still problems, please provide clear information about your requirements and screenshots of the expected results, I will answer you as soon as possible.


    Best Regards,
    Henry


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