Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowGet certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started
I've tried all the solutions provided by folks before but non resolve my issue!
I need a simple calculation of last month value of a KPI callled OTE. but when i use below formulas the answer is either zero or same value as this month!
1- Answer is same value as current month value:
OTE Last Month = CALCULATE([AVG OTE Score], DATEADD('Calendar'[Date].[Date],-1,MONTH))
2- Answer is Zero:
OTE Last Month = CALCULATE([AVG OTE Score], PREVIOUSMONTH('OTE-FCR'[INTERACTION_DATE]))
for first formula I have used a calendare table as required so problem is not there.
I do appreciate any corrective ideas
Thanks!
Solved! Go to Solution.
@Anahita,
Please check the DAX and visual in attached PBIX file.
Regards,
Lydia
Thanks a lot for your support, Actually I found the problem that was I used a derived column called month name out of the main date coulmn and was trying to have that month column on my x axis when I switched that for original date column everything's working fine. it seems to be a bug maybe in power bi that derivitve columns don't act the same as original columns but neverthelss I explained here in case someone will have same issue later on .
@Anahita,
Please check the DAX and visual in attached PBIX file.
Regards,
Lydia
@Anahita,
Please check more details about CALENDARAUTO() function here.
In your sceanario, what fields do you use to create the visual? Please share your PBIX file through OneDrive or Dropbox.
Regards,
Lydia
Thanks a lot for your support, Actually I found the problem that was I used a derived column called month name out of the main date coulmn and was trying to have that month column on my x axis when I switched that for original date column everything's working fine. it seems to be a bug maybe in power bi that derivitve columns don't act the same as original columns but neverthelss I explained here in case someone will have same issue later on .
Dear Lidya,
Thanks a lot for your kind responce, yet this formula returns same value of the current month with my data, though I found a difference in your calendare table comparing ot mine: Calendar = CALENDARAUTO()
what is above formula for? I tried adding a new measure exactly like that but when I didn't add anything inside parenthesis nothing happened.
Are you filtering the visual or the report page to the current month so that the date add can work?
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |