March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have this calculation that by a filter of Product, if there is no product for the current month it brings back the previous month, i dont quite understand how its doing it but if there i no result i just want it to bring back 0 rather than using the last months figure
In Month Revenue = CALCULATE(
SUM('Registrations'[MRR]),
FILTER(
'Fiscal Date',
FORMAT(MONTH(NOW()),"MMM") = 'Fiscal Date'[Month]
&& YEAR(Now()) = 'Fiscal Date'[Fiscal Year] )
)
Proud to be a Super User!
Solved! Go to Solution.
That's kind of odd. I created a table "Registrations" with MRR and Date columns and a Fiscal Date table with Date, Month and Year columns, related Fiscal Date to Registrations on the Date columns and actually got back the sum for January even though it is currently February and even though I had data for February. Will have to take a look at it more to understand what is going on and not even sure if this simulates your model correctly. However, I was able to correct the issue by using this formula:
In Month Revenue = CALCULATE( SUM('Registrations'[MRR]), FILTER( Registrations, FORMAT(MONTH(NOW()),"MMM") = FORMAT(MONTH([Date]),"MMM") && YEAR(NOW()) = YEAR([Date]) ))
So, basically, just used the Registrations table and removed the date table from the mix.
That's kind of odd. I created a table "Registrations" with MRR and Date columns and a Fiscal Date table with Date, Month and Year columns, related Fiscal Date to Registrations on the Date columns and actually got back the sum for January even though it is currently February and even though I had data for February. Will have to take a look at it more to understand what is going on and not even sure if this simulates your model correctly. However, I was able to correct the issue by using this formula:
In Month Revenue = CALCULATE( SUM('Registrations'[MRR]), FILTER( Registrations, FORMAT(MONTH(NOW()),"MMM") = FORMAT(MONTH([Date]),"MMM") && YEAR(NOW()) = YEAR([Date]) ))
So, basically, just used the Registrations table and removed the date table from the mix.
that works great as a work around, let me know if you figure out what issue is, im still prodding at it, but its a wierd one, do you think its a bug?
I also posted on SQL BI and Marco Russo suggested i create a calculated table but alas im using direct query mode
Proud to be a Super User!
glad i am not going crazy!
Will give that a bash thanks.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |