Forum Discussion
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 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?
slicer
columns:fiscalyear,fiscaquarter,fiscalmonth
values:Current Revenue,Total Revenue Pre
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.
4 Replies
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 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:
- Removed fiscalquarter from the comparison since fiscalmonth is sufficient to match the period.
- 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
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 LinkedInHi, 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.
