Forum Discussion

questionac's avatar
questionac
New Member
1 year ago
Solved

DAX formula / matrix / date

In the matrix table: Rows: Department (selected through the slicer) Columns: Year and month (selected through the slicer) Values: The sales for the selected year and month, as well as the sales...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, questionac 

     

    I've modeled some data to hopefully fit your situation. You can try the following formula.

     

    Total Revenue Pre = 
CALCULATE ( SUM ('MMSOpptunities'[Current Revenue] ),
    FILTER ( ALLEXCEPT( MMSOpptunities,MMSOpptunities[fiscalquarter],MMSOpptunities[fiscalmonth]),
        [fiscalyear] = MAX(  'MMSOpptunities'[fiscalyear] ) - 1

))

     

    Is this the result you expected? If it did not solve your problem, please provide more data.

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

dharmendars007's avatar
dharmendars007
Memorable Member
1 year ago

Hello questionac , 

 

Please us the below revised measure..


Total Revenue Pre =
CALCULATE(
SUM('MMSOpportunities'[Current Revenue]),
FILTER(
ALL('MMSOpportunities'),
'MMSOpportunities'[fiscalyear] = SELECTEDVALUE('MMSOpportunities'[fiscalyear]) - 1 &&
'MMSOpportunities'[fiscalmonth] = SELECTEDVALUE('MMSOpportunities'[fiscalmonth])))

 

Key Changes:

  1. Removed fiscalquarter from the comparison since fiscalmonth is sufficient to match the period.
  2. Kept ALL to remove filters only on fiscalyear and fiscalmonth while retaining the Department filter.

 

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

 

Thank You

Dharmendar S

LinkedIN