Custom calendar calculate..need value from last month to show in 1st week of this month

[Anonymous]

Hi,Everyone

Really need your help to get me through this...

I'm want to calculate gap from last month vs this month ,

and gap result should show in 1st week of each month...

my calendar is fiscal calendar...

from example using "DATEADD" which cause it wrong ,

how can i allocate last month offset to 1st week of each month ?

[amitchandak]

@Anonymous 

You can try like with time intelligence and date calendar. But need data for your exact case

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Greg_Deckler]

Sample data always helps. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

I thought that there was a PREVIOUSMONTH time intelligence function. Also, you can always do time intelligence calculations yourself and skip the hassle, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Thank you Greg & Amitchandak for your help ,Maybe i'm too new to understand the solution but i'm try to learn "earlier" to get the condition to a result i need 🙂

[Anonymous · Accepted solution]

i can fix it wiht unprofesstional code below..

---

Last Month Offset = CALCULATE([This Month Offset],FILTER(ALL('Calendar'),'Calendar'[WDMonthNo]=MAX('Calendar'[WDMonthNo])-1 && 'Calendar'[WeekOfMonth]=MIN('Calendar'[WeekOfMonth])&&'Calendar'[WDYearNo]=SELECTEDVALUE('Calendar'[WDYearNo])))

[Anonymous]

Hi @Anonymous,

You have solved the problem by yourself, right?

Best Regards

Rena

[Anonymous]

Yes,Rena . i solved it by myself.

[Anonymous]

Hi @Anonymous ,

Thanks for your reply. Could you please mark your last post as solution? It may help others if they face the similar problem. Thank you.

Best Regards

Rena