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 Guys...
i have this simple model:
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:
Now i want to running total, but set the period from day 26 (current month) to day 25 (next month)...
Someone help me?
Cheers
Solved! Go to Solution.
@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])))
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
Specific month selected screen shot
If you find this works for you, please give as many KUDOS as you can.
Cheers
CheenuSing
@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
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
Specific month selected screen shot
If you find this works for you, please give as many KUDOS as you can.
Cheers
CheenuSing
@CheenuSing , how we can calculate sales for last month, last 3 months and ytd sales. Please help.
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:
and so on?
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:
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.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |