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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
garcia
Frequent Visitor

Total Sales X Custom Period (day 26 current month to day 25 next month)

Hi Guys...

i have this simple model:

 

model1.PNG

 

 

 

 

Measure to total sales:

Total Rev = SUM(fSales[Valor_Venda] )

 

Then i create a Total Month to Date measure, that gives me a month period:

Rev MTD = CALCULATE([Total Rev];DATESMTD(dCalendar[Data]))

 

Create a table visualization like this:

 

table.PNG

 

Now i want to running total, but set the period from day 26 (current month) to day 25 (next month)... 

 

Someone help me?

 

Cheers

2 ACCEPTED SOLUTIONS
lalthan
Resolver II
Resolver II

@garcia Try the below formula

 

Rev MTD = calculate([Total Rev] ,datesbetween(dCalendar[Data],date(year(lastdate(dCalendar[Data])),switch(true(),day(lastdate(dCalendar[Data]))< 26,month(lastdate(dCalendar[Data]))-1,month(lastdate(dCalendar[Data]))),26)
   ,lastdate(dCalendar[Data])))

View solution in original post

Hi @garcia

 

Here is my approach to define the Months based on 26 to 25 dates.

I am assuming you have a calendar table with Date as a column

 

1. Define a column in the calendar table 

   MonNo2625 = IF ( Day([Date]) < 26,(  If ( Format(Dateadd(Calendar[Date],-1,MONTH),"mmm") ="",
                                                                 Month(Date(Year(Calendar[Date]),MONth(Calendar[Date])-1,01)
                                                                        ),
                                                                       Month(Dateadd(Calendar[Date],-1,MONTH)) ) )
                                                              ,
                                                             Month('Calendar'[Date])

                                                       )

This will generate a month number based on 26 to25 dates. 

2. Define a column in the calendar table 

   MonYr2625 = IF ( Day([Date]) < 26, (If ( Format(Dateadd(Calendar[Date],-1,MONTH),"mmm") ="" ,
                                                                    Format(Date(Year(Calendar[Date]),MONth(Calendar[Date])-1,01) ,"MMM"),
                                                                                 Format(Dateadd(Calendar[Date],-1,MONTH),"mmm") ) ) & "-" &
                                                                                       Format('Calendar'[Date],"mmm") &"-" &
                                                                                      (If ( Format(Dateadd(Calendar[Date],-1,MONTH),"mmm") ="" ,
                                                                                      Format(Date(Year(Calendar[Date]),MONth(Calendar[Date])-1,01),"YYYY"),
                                                                                           Format(Dateadd(Calendar[Date],-1,MONTH),"YYYY") )
                                                                                        )
                                                                                             , 
                                                                                        Format('Calendar'[Date],"mmm") &"-" &
                                                                                         (If ( Format(Dateadd(Calendar[Date],1,MONTH),"mmm") ="" ,                                                                                                        Format(Date(Year(Calendar[Date]),MONth(Calendar[Date])+1,01),"MMM"),
                                                                                       Format(Dateadd(Calendar[Date],1,MONTH),"mmm") ))
                                                                                         & "-" &
                                                                                        Format(Calendar[Date],"yyyy")
                                                                                     )

 

This will create month year as Dec-Jan 2014, Aug-Sep 2014 etc.

3. Create a column in the calendar table

     Year2625 = IF ( Month([Date]) = 01 && Day([Date]) < 26, Year([Date]) - 1, Year([Date])  )

     This will create year based on 26 to 25 dates.  For Dec 26 to Jan 25 it will be the year of Dec and not of Jan.

4. Select the column MonYr2625 defined at step 2.  In the Modelling tab set the sort by column for this as MonNo2625.

5. Create filters for the above defined columns at step 1,2 and 3.

6. Create a matrix report with the values as Celndar[Date] and Your measure for sales or what ever. 

7. You will see the magic working for you.

 

Some sample screen shots based on my data

Capture.GIF

 

Specific month selected screen shot

Capture.GIF

 

If you find this works for you, please give as many KUDOS as you can.

 

Cheers 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

9 REPLIES 9
lalthan
Resolver II
Resolver II

@garcia Try the below formula

 

Rev MTD = calculate([Total Rev] ,datesbetween(dCalendar[Data],date(year(lastdate(dCalendar[Data])),switch(true(),day(lastdate(dCalendar[Data]))< 26,month(lastdate(dCalendar[Data]))-1,month(lastdate(dCalendar[Data]))),26)
   ,lastdate(dCalendar[Data])))

@lalthan simply fantastic! Just Perfect!

 

 I´m so grateful for your time and the fabulous solution!

 

Thanks thanks thanks....

Hi @garcia

 

@lalthan method is simple and short and perfect.

 

Only thing I wanted to seek your clarification is, if you select the month slicer say Jan 16 , you want to consider the month dates as 26 Jan 2016 to 25 Feb 2016 and show only the dates in this range.

 

In this solution if you select Jan 2016 it will show the cumulation of 1 to 25 and then reinitialise the total from 26 to 31st.

 

I have a different approach if you want to reclassify months from 26 to 25th of the following month.

 

Appreciate your reply.  

 

Cheers 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing, the solution of @lalthan was perfect, cause i used a table to display.

 

However the solution you suggested to reclassify months from 26 to 25th of the following month (where a month slicer can be used) is also interesting, can you show us?

 

Thanks for your atention!

Hi @garcia

 

Here is my approach to define the Months based on 26 to 25 dates.

I am assuming you have a calendar table with Date as a column

 

1. Define a column in the calendar table 

   MonNo2625 = IF ( Day([Date]) < 26,(  If ( Format(Dateadd(Calendar[Date],-1,MONTH),"mmm") ="",
                                                                 Month(Date(Year(Calendar[Date]),MONth(Calendar[Date])-1,01)
                                                                        ),
                                                                       Month(Dateadd(Calendar[Date],-1,MONTH)) ) )
                                                              ,
                                                             Month('Calendar'[Date])

                                                       )

This will generate a month number based on 26 to25 dates. 

2. Define a column in the calendar table 

   MonYr2625 = IF ( Day([Date]) < 26, (If ( Format(Dateadd(Calendar[Date],-1,MONTH),"mmm") ="" ,
                                                                    Format(Date(Year(Calendar[Date]),MONth(Calendar[Date])-1,01) ,"MMM"),
                                                                                 Format(Dateadd(Calendar[Date],-1,MONTH),"mmm") ) ) & "-" &
                                                                                       Format('Calendar'[Date],"mmm") &"-" &
                                                                                      (If ( Format(Dateadd(Calendar[Date],-1,MONTH),"mmm") ="" ,
                                                                                      Format(Date(Year(Calendar[Date]),MONth(Calendar[Date])-1,01),"YYYY"),
                                                                                           Format(Dateadd(Calendar[Date],-1,MONTH),"YYYY") )
                                                                                        )
                                                                                             , 
                                                                                        Format('Calendar'[Date],"mmm") &"-" &
                                                                                         (If ( Format(Dateadd(Calendar[Date],1,MONTH),"mmm") ="" ,                                                                                                        Format(Date(Year(Calendar[Date]),MONth(Calendar[Date])+1,01),"MMM"),
                                                                                       Format(Dateadd(Calendar[Date],1,MONTH),"mmm") ))
                                                                                         & "-" &
                                                                                        Format(Calendar[Date],"yyyy")
                                                                                     )

 

This will create month year as Dec-Jan 2014, Aug-Sep 2014 etc.

3. Create a column in the calendar table

     Year2625 = IF ( Month([Date]) = 01 && Day([Date]) < 26, Year([Date]) - 1, Year([Date])  )

     This will create year based on 26 to 25 dates.  For Dec 26 to Jan 25 it will be the year of Dec and not of Jan.

4. Select the column MonYr2625 defined at step 2.  In the Modelling tab set the sort by column for this as MonNo2625.

5. Create filters for the above defined columns at step 1,2 and 3.

6. Create a matrix report with the values as Celndar[Date] and Your measure for sales or what ever. 

7. You will see the magic working for you.

 

Some sample screen shots based on my data

Capture.GIF

 

Specific month selected screen shot

Capture.GIF

 

If you find this works for you, please give as many KUDOS as you can.

 

Cheers 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

@CheenuSing ,  how we can calculate sales for last month, last 3 months and ytd sales. Please help.

Greg_Deckler
Super User
Super User

Can you clarify? Are you saying that on Day 25, you want to restart your calculation of the running total for the month? So, the first few "months" would be:

 

  • Dec 25th of last year to January 25th of this year
  • January 26th - February 25th
  • February 25th - March 25th

and so on?


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi smoupre, thanks for reply...

 

Exactly! You´re correct!

 

For each day 26th I start a new cycle ending on the day 25th of the next month:

 

  • Dec 25th of last year to January 25th of this year
  • January 26th - February 25th
  • February 26th - March 25th
  • March 26th - April 25th 

and so on!!!! 

So, I think that the *right* way to solve this issue is to modify your calendar table to this custom format, so, in your calendar table, it would look something like:

 

DateKey,Day,Month,Year

1/24/2016,24,1,2016

1/25/2016,25,1,2016

1/26/2016,1,2,2016

1/27/2016,2,2,2016

 

Something along those lines that. The reason that I say this is the *right* way to fix it is that then all of your time intelligence functions should continue to work. 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.