Dax query - How to Filter with If Condition in Pagination Report

[Suneer]

Our company maintaning 2 databases Historical & Current Production Database. I want to filter the details of Inventory by the follwing conditions to use in pagination report

if Historical data then month = 4 (For Example) and If it is production data then month <=4

Something like this, please help

FILTER(VALUES('StockAnalyzerUnion'[Month]),'StockAnalyzerUnion'[Month] --- If ('StockAnalyzerUnion'[Data] ="Historical",4,<=4)))

[Suneer]

Hi @tamerj1 

Thank you for your response, i applied the way you explained, it is working for currenct data but not filtering for the month of 4 for Historical Data but summarizing with all month (From 1 to 4) .

Month	Historical	Current
1	1000	500
2	1200	600
3	1400	300
4	1500	400
Total	5100	1800

as per the above example,  historical data should bring 1500 but it showing 5100 (Total) , current data should show only the summary 1800 which is correct

Please see the Dax query which i applied mentioned below

EVALUATE SUMMARIZECOLUMNS('StockAnalyzerUnion'[Branch],
'StockAnalyzerUnion'[Company],
'StockAnalyzerUnion'[Data],
'StockAnalyzerUnion'[Division Name],
'StockAnalyzerUnion'[Group Name],
'StockAnalyzerUnion'[Location],
'StockAnalyzerUnion'[Month],
'StockAnalyzerUnion'[Year],
'StockAnalyzerUnion'[Qty],
'StockAnalyzerUnion'[Value],
FILTER (VALUES ( 'StockAnalyzerUnion'[Month]),
IF ( CALCULATE ( SELECTEDVALUE ( 'StockAnalyzerUnion'[Data] ) ) = "Historical",
'StockAnalyzerUnion'[Month] = 4,
'StockAnalyzerUnion'[Month] <= 4)))

but this problem has been resolved by using the calculated field feature in report builder. 

Thank you once again 

[tamerj1]

@Suneer 

Calculated tables cannot interact with the filter context hence are not dynamic. What exactly are you trying to accomplish?

[Suneer]

Recently our company had implemented new ERP.  In the new ERP, if we want to run the inventory upto April we should start from the begining where the old ERP is running by month wise only (no need to run from the begining). Both data are available as power bi dataset.  But the problem is if i want to compare both inventory for the month of April,  The new ERP should take the data from the begining where old ERP by that particular month

As i mentioned this problem has been resolved by using calculation fiield, i made 2 calulation fields both Old and New . 

[tamerj1]

Hi @Suneer 

Please try

=
FILTER (
VALUES ( 'StockAnalyzerUnion'[Month] ),
IF (
CALCULATE ( SELECTEDVALUE ( 'StockAnalyzerUnion'[Data] ) ) = "Historical",
'StockAnalyzerUnion'[Month] = 4,
'StockAnalyzerUnion'[Month] <= 4
)
)