Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vanessafvg
Super User
Super User

DAX Inmonth result brings back last month instead of current month

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] )
)





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

https://disqus.com/home/discussion/daxpatterns/time_patterns_dax_patterns/?utm_source=reply&utm_medi...

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




glad i am not going crazy!

 

Will give that a bash thanks.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.