Forum Discussion

vinoothna9's avatar
vinoothna9
Frequent Visitor
5 years ago
Solved

Relationship/Dax Function

I have a requirement where I have to represent revenue amount in a bar chart and  Fiscal year, Quarter as Slicers. - Revenue Amount is from Revenue table.  - Fiscal year, Quarter slicers are from D...
Need Help
Show and Tell
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi vinoothna9 ,

    The reason that cause this issue is that you diable the date relationship between two tables so that the fiscal year slicer could not work and the visual will show both fiscal years value based on your initial measure.

    You can modify your measure like this:

    Revenue Amount = 
CALCULATE (
    SUM ( 'Revenue Table'[Revenue] ),
    FILTER (
        'Revenue Table',
        YEAR ( 'Revenue Table'[Date] )
            = SELECTEDVALUE ( 'Default Calendar'[Fiscal year] )
    ),
    USERELATIONSHIP ( 'Default Calendar'[Quarter], 'Revenue Table'[Quarter] )
)

    In my sample use the table visual to show the result directly:

    Attached the sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
5 years ago

vinoothna9  This should not need any special measures. If the relationship is already there, it will work, but relationship must be on Date column NOT quarter columns. 

 

Revenue measure should simply look like: 

 Total Revenue = Sum('Revenue Table'[Revenue])

 

Put that in the revenue visual. Relate Revenue[Date] to Date[Date]. In slicers, use Date[FiscalQuarter] and Date[FiscalYear]

 

Not sure how you've created the date table and columns? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html