Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a function to calculate the PMTD (previous month to date) sales using the function below
PreviousMTD = TOTALMTD( SUM(Table[Value]) , DATEADD('Date'[Date], -1 , MONTH) )
I will like to to take into consderation the number of days for the current month.
For instance for Oct 4, the PTMD will only calculate 4 days in september since we are only 4 days in october.
Any idea how to achieve this?
Thank you
Solved! Go to Solution.
Hi @Nanakwame
If I am reading correctly, you are quite close. I recommend creating three measures. The first is to sum the relevant values. The second to sum only the portion of the Current Month To Date. The third is to mimic the position of the Current Month To Date measure.
Hope this helps! 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi Theo,
i tried the approach you provided and it seems like its calculating the 6 days for october comparing all of september. I will prefer it compare it to only 6 days in september as well and so on.
Hi @Nanakwame
If I am reading correctly, you are quite close. I recommend creating three measures. The first is to sum the relevant values. The second to sum only the portion of the Current Month To Date. The third is to mimic the position of the Current Month To Date measure.
Hope this helps! 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
I found this response very helpful. I've been looking for this answer for awhile now.. thank you so much. It works perfectly for me.
@tyjames05 glad it helped, mate!
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Hi Theo,
i tried the approach you provided and it seems like its calculating the 6 days for october comparing all of september. I will prefer it compare it to only 6 days in september as well and so on.
Hi @Nanakwame ,
You can try these measures to compare:
CurrentMonthValue =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Date',
'Date'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
&& 'Date'[Date] <= TODAY ()
)
)
PreviousMonthValue =
VAR currentmonthstart =
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR currentday =
TODAY ()
VAR currentdiff =
DATEDIFF ( currentmonthstart, currentday, DAY )
VAR previousmonthstart =
EOMONTH ( TODAY (), -2 ) + 1
VAR PreviousMonthValue =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Date',
'Date'[Date] >= previousmonthstart
&& 'Date'[Date] <= previousmonthstart + currentdiff
)
)
RETURN
PreviousMonthValue
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I can't tell how long I've ben looking for this solution - Thanks a lot
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |