Forum Discussion

ravi_609's avatar
ravi_609
Helper I
5 years ago
Solved

How to Compare Two Column Dynamically?

I want to compare custom column with Fiscal Year. when I have set Filter in Slicer "Sales Period" & Compare with any year data with Slicer "Compare To".   I can not get any solution to make it wor...
  • ravi_609's avatar
    5 years ago

     amitchandak 
    Hello Sir, I have got the solution
    Your solution is nearby same but with the relation of two date table my slicer also filtered.

    So I have create Two table with My financial Year column 

    First Table Name =Compare From
    Second Table Name = Compare To

     

    Compare From = VALUES(Financials[Financial Year])

     

    Then Second

    Compare To = VALUES(Financials[Financial Year])

     

    Then we create one measure for remove same selected value from other slicer

    So the measure for this 

    FY Selection Control = IF(SELECTEDVALUE('Compare From'[From Financial Year]) = SELECTEDVALUE('Compare To'[To Financial Year]),0,1)

     

    Then apply this measure in visual level filter & set value to "1".

     

     

    Then Create Two measure for Calculate Sales :

    From Sales = CALCULATE([Total Sales],FILTER(Financials, Financials[Financial Year] IN {SELECTEDVALUE('Compare From'[From Financial Year])}))​

     

    To Sales = CALCULATE([Total Sales],FILTER(Financials, Financials[Financial Year] IN {SELECTEDVALUE('Compare To'[To Financial Year])}))​

     

    Then apply this two measure in your visual.

     

  • ravi_609's avatar
    5 years ago

    Here is the screenshot for the flow:

    Two new tables unrelated to any other table in the model.

    Screenshot (107).png

    Create like this :

    Screenshot (108).png

    Screenshot (109).png

    The selection filter is then applied at the visual level as follows:

    Screenshot (110).png

    The last sales measure is then applied to the visual.

    Here's the last output:

    Screenshot (111).png