Compare Dates from different tables and dynamic Dax

[Anonymous]

 

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

 

[KeerthiReddy_1]

Hello Sir,

I have the similar kind of Question. 

I am just started using my Power BI.

I have 2 different exel Sheets loaded to Power BI.

Sheet 1 contains all the details with some code numbers.

sheet 2 contains the data which needs to be corrected with few codes.

for ex: sheet 1 has all the codes 1,2,3,4.......300

sheet 2 has codes: 2(from sheet 1) needs to replaced with 1 and 15 (from sheet 1) needs to replace with 2 and so on.

Now 1 want to add a new column in power BI(sheet 1)

like: if sheet 1 has codes with 1,2,and >52 then it needs to replced with codes from sheet 2.

Can you please help me on this

[Anonymous]

Can somebody help on this please

[Anonymous · 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.

[amitchandak]

@Anonymous , 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))

[Anonymous]

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! 

[amitchandak]

@Anonymous , 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

[Anonymous]

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.

 

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.

 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.