topic Re: Need help with a dax query in DAX Commands and Tips

Need help with a dax query

Taniya — Thu, 30 Apr 2020 17:38:01 GMT

Hi
So below is the screenshot of Rref(Risk reference ),qref(quote reference),start date,end date and annual gross premium(which we need to calculate). What we have achieved correctly is annual gross premium column but what not is the summation it is providing. As you see Rref is same in all cases so for a particular Rref and its dintinct Qref we want only annual gross premium whose start date is latest among the two same qref for a risk in that club year .
Ex for 2017 annula gross premium should be 10000,for 2018 it will be 10000 for 2019 = 9986.33 ,for 2020 =9986.33,for 2021 = 9986.33. sos alltogether it should be 49958.99 but what we are getting as total is 29.986.33.
DAX for what we have achieved so far is also attached below -

Annual Gross perm = IF([Distinct Count]=2,

SUMX(

FILTER(

GROUPBY( 'Club Year Data','Club Year Data'[Rref],'Club Year Data'[Qref],'Club Year Data'[spl_stagingid], 'Club Year Data'[spl_perdaygrossperm],'Club Year Data'[productname],'Club Year Data'[spl_startdate],'Club Year Data'[Trim name]),

SELECTEDVALUE('Club Year Data'[Club Year])=YEAR('Club Year Data'[spl_startdate])),

'Club Year Data'[spl_perdaygrossperm]*365),

SUMX( GROUPBY( 'Club Year Data','Club Year Data'[Rref],'Club Year Data'[Qref],'Club Year Data'[spl_stagingid], 'Club Year Data'[spl_perdaygrossperm],'Club Year Data'[productname],'Club Year Data'[Trim name]),

'Club Year Data'[spl_perdaygrossperm]*365) )
Please Note - Distint Count=DISTINCTCOUNT(Club Year Data[qref]) and Trim name is the name of broker
Please help me with this.

Re: Need help with a dax query

Anonymous — Thu, 30 Apr 2020 21:11:57 GMT

Hi Taniya ,

To perform any aggregrate function using group by, you need to use CURRENTGROUP(). I do not see you using this anywhere in you formula.

Please check this article

https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

Also, please have a look at the below video.

https://curbal.com/blog/glossary/groupby-dax-example

It is very diffcult to follow you question. Please share some sample data to help you further on this.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Need help with a dax query

Taniya — Fri, 01 May 2020 04:38:21 GMT

is q1 and q2 are two quotes attaches to the same risk and if end date of q1 =start date of q2 then only consider that date for q2 i.e the latest quote