DAX formula / matrix / date

[questionac]

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 for the same month in the previous year.

The DAX function is written as follows, but it does not work correctly. Where should I make corrections?

dax

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

slicer
columns:fiscalyear,fiscaquarter,fiscalmonth
values:Current Revenue,Total Revenue Pre

[Anonymous · Accepted solution]

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.

[Kedar_Pande]

@questionac 

You can try:

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

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[dharmendars007]

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 

[questionac]

data↓（example)

Current Revenue	Close Date (Expected Signing or Date Won/Lost/WD)	Zanken.Tower	Zanken.Team	fiscal-date	fiscalyear	fiscalquarter	fiscalmonth	monthnumber
184	2022年9月29日	アプリ生産管理	生産管理_尾道	2022/1/29 0:00	2022	Q2	01月	5
109	2022年9月29日	アプリ生産管理	生産管理_尾道	2022/1/29 0:00	2022	Q2	01月	5
142	2022年9月29日	アプリ生産管理	生産管理_尾道	2022/1/29 0:00	2022	Q2	01月	5
185	2022年9月29日	アプリ生産管理	生産管理_尾道	2022/1/29 0:00	2022	Q2	01月	5
157	2022年9月29日	アプリ生産管理	生産管理_尾道	2022/1/29 0:00	2022	Q2	01月	5