cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rajashekhar
Helper I
Helper I

Compare Dates from different tables and dynamic Dax

Query12.PNG

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 column from date dimension table.

There is a filter date field from date dimension. I need the following DAX for below sql.

case when (A.[Financial Date] <> '1900-01-01 ' and A.[Financial Date] <= '27/1/2020)

then (A.[Cost Amount] + A.[Cost Amount Adjustment]) else (A.[Cost Amount Physical]).

 

27/1/2020 should be dynamic when we pass 24/1/2020.

 

But i need the value of selectedvalue from slicer 

Should be the value from corresponding financial date not the value from date table.

Example, the financial date is 27/2/2020 , corresponding transaction date is 24/3/2020. When we 24/3/2020, it should take 27/2/2020 in the Dax.

I have around 7 million rows so just pasted a sample data.

Kindly help. Thanks in advance

 

1 ACCEPTED SOLUTION

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.

View solution in original post

6 REPLIES 6
Rajashekhar
Helper I
Helper I

Can somebody help on this please

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.

View solution in original post

amitchandak
Super User IV
Super User IV

@Rajashekhar , Prefer to have an independent date table in this case and try measure

 

measure =
var _max = maxx(Filter(allselected(A), [Transaction Data] = selectedValue(Date[Date])), A[Financial Date])
return
calculate(sumx(A,A[Cost Amount] + A[Cost Amount Adjustment]), filter(A, A[Financial Date] <=_max)) +calculate(sum(A[Cost Amount Physica]), filter(A, A[Financial Date] >_max))

 

 

If date is connected
measure =
var _max = maxx(Filter(allselected(A), [Transaction Data] = selectedValue(Date[Date])), A[Financial Date])
return
calculate(sumx(A,A[Cost Amount] + A[Cost Amount Adjustment]), filter(A, A[Financial Date] <=_max),all(Date)) +calculate(sum(A[Cost Amount Physica]), filter(A, A[Financial Date] >_max),all(date))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

First of all thank you so much Amit for the quick response.

But the financial date is not always greater than transaction date, sometimes it is less than transaction date . So there will be mismatch in the variables for choosing Max! 

@Rajashekhar , I am trying to take max on the selected date. [Transaction Data] = selectedValue(Date[Date]) , We have choose between in min or max  [Financial Date] on selected Transaction date



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Actually there is a filter on the transaction date . I guess we need to add it. Please find below query. I have added posting filter in ur query but dont know how to add transaction date filter.

selectB.[Warehouse Code],d.[Item Number],(A.Quantity) as Invonhand,case when (A.[Financial Date] <> '1900-01-01 00:00:00.000' and A.[Financial Date] <= '2020-08-31 00:00:00')        then (A.[Cost Amount] + A.[Cost Amount Adjustment])       else (A.[Cost Amount Physical])end as invvalue ,A.*from [dbo].[FactInventoryValue] A inner join [dbo].[DimInventoryDimension] Bon A.[Inventory Dimension Key] = B.[Inventory Dimension Key]inner join [dbo].[DimCompany] con A.[Company Key] = c.[Company Key]and c.[Company Name] = 'BRIGHTSTAR URUGUAY S.A.'inner join [dbo].[DimProduct] don A.[Product Key] = d.[Product Key]where A.[Posting Type] in ('Physical','Financial')and A.[Transaction Date] <= '2020-08-31 00:00:00'and A.[Transaction Date] <> '1900-01-01 00:00:00.000' ) tmpgroup by [Warehouse Code],[Item Number].

 

Here is the query which u gave.

 

AmitQuery =

 

var _max = MAXX(Filter(allselected('1 Inventory Value'), [TRANSACTIONAL_MONTH_YEAR] = selectedValue('Date'[Month])), '1 Inventory Value'[FINANCIAL_MONTH_YEAR])

 

return

 

calculate(sumx('1 Inventory Value','1 Inventory Value'[Cost Amount] + '1 Inventory Value'[Cost Amount Adjustment]), filter('1 Inventory Value', '1 Inventory Value'[FINANCIAL_MONTH_YEAR] <=_max &&'1 Inventory Value'[Posting Type] in {"Physical","Financial"} &&'1 Inventory Value'[Financial Date]<>DATE(1900,1,1) ),ALL('Date'[Month])) + calculate(sum('1 Inventory Value'[Cost Amount Physical]), filter('1 Inventory Value', '1 Inventory Value'[FINANCIAL_MONTH_YEAR] >_max && '1 Inventory Value'[Posting Type] in {"Physical","Financial"} && '1 Inventory Value'[Financial Date]= date(1900,1,1)),ALL('Date'[Month]))

 

Filter is month year format which i changed accordingly.

Amit.PNG

Here we should the value 1,499,767,50 but we are getting 1,453,419.00.

 

The filter given is Feb 2020.

 

Another example below.

amIT 1.PNG

 Here we need to get 605,142.92 but as the data is getting filtered for feb 2020, we are getting 0!!! And there are other 2 filters one is year and another is company name. 

 

 

 

 

 

 

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.