Sum of sales for fiscal calendar month dates

[Anonymous]

Hi,

I have my calender table set up and I need help to get the sum of previous month and prior to previous month for the fiscal dates.

my fiscal calender  FY21 starts from 22nd Jun.

->So 22nd Jun- to 27th July is consider as 1st Month of FY

-> 28th July- 22nd Aug  is consider as 2nd Month of FY

--> so on..

I used this formula:

Previous month:- 

TOTALMTD(table[Total Sales Revenue],DATEADD(FiscalCalendar[Date],-1,MONTH))

but this above formula calculates sum for Aug1st-Aug 31st which is not what i am looking for.

I want to calculate the sum for these dates range: Previous month should take fiscal calender dates 28th July- 22nd Aug

Prior Previous month:- 

TOTALMTD(table[Total Sales Revenue],DATEADD(FiscalCalendar[Date],-2,MONTH))

but this above frmula calculates sum for July1st-July31st

 Prior previous month should take fiscal calender dates Jun 22nd - July 27th to sum the sales .

 Sample of data attached. ( assume all the relationships are built and this sample file is the out put in a table visualization in power bi desktop)

Any help.

Thanks,

Tejaswi

[amitchandak · Accepted solution]

@Anonymous , Time Intelligence will not work for you.

Create a month Rank on the month start date. Make sure you have a separate month/Date table for that

And Try measures like

Month Rank = RANKX(all('Date'),'Date'[Month Start Date ],,ASC,Dense)
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
Last year Month= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=(max('Date'[Month Rank]) -12)))

Refer my Month and Week Blog

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Dane]

Good morning,

I am trying your formula and am getting an erroneous result. 

Using the formula you have for MTD, I would expect my result to be $1,350.64, however I am getting everything

 below is the formula I am using

Can you let me know what I am doing wrong?

[Anonymous]

Hi @amitchandak ,

I am trying to follow the steps you mentioned, I created Month start of date:

Month Start date = STARTOFMONTH(zcvrFiscalCalendar[Date])

However, my start dates takes calendar date and not fiscal date. so rank formula also changes doesn't give me correct output.

attached the screenshot for your reference.. Could you please help!

Thanks,

Tejaswi 

[Anonymous]

Hi @amitchandak ,

Any updates?

 Did you get a chnace to go over?

Thanks,

Tejaswi 

[amitchandak]

@Anonymous , my assumption is that in your custom calendar you already have the start of the month. You can not use the start of month function of power bi.

If you do not have, give me a year calendar in excel I will try to create 

[Anonymous]

Hi @amitchandak 

Thanks for your reply.

Please find atatched.

My dataset has both the calendar and fiscal attribute,, so if user wants to see the data for calender, the switch between fiscal and calendar attribute will help them. so we have added the month and attribute to the dataset. ( you can see this in my attached).

Appreaciate our help!

Thnaks,

Tejaswi

[Anonymous]

Thanks @amitchandak & @Greg_Deckler , I will go thru the blogs and will get back for any questions.

Thanks,

Tejaswi

[Greg_Deckler]

@Anonymous - OK, if you have a custom fiscal calendar then time "intelligence" functions are next to useless. You will need to create the filters yourself.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, 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-TITHW/m-p/434008

If those don't help, @ me and I'll take a deeper look at your PBIX file.