dax date parameter

[fab196]

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	13/10/2021
3	mumbai	9/10/2021	13/10/2021
5	mumbai	11/10/2021	13/10/2021
5	guj	11/10/2021	13/10/2021
6	mumbai	9/10/2021	11/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 

[v-henryk-mstf]

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.

[v-henryk-mstf · Accepted solution]

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]

@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

[parry2k]

@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.

[parry2k]

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

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]

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