Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys, hope you can help me out here.
I am having some trouble coming up with a solution for a problem I'm facing:
We are supposed to display sales on a monthly basis.
However, there are two sets of Sales data, "Monthly" and "Daily".
The "Monthly" data contains the most accurate the data and will only typically ready (Consolidated) by the "15th" of the following month.
E.g> Today is 2nd of March, which means that the "Monthly" data of Feburary will not be ready until the "15th" of March.
The "Daily" data, is collected daily but should not be used for reporting as much as possible.
This is a screenshot of what I currently have:
The chart above is currently only showing Monthly data.
How can I create a measure to show a combination of Monthly and Daily data?
If today is 2nd March > Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January "Monthly" sales data + Sum of 1st Feburary to 2nd March "Daily" sales data.
If today is 16th March > Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January to Feburary "Monthly" sales data + 1st to 16th March "Daily" sales data.
Selecting previous Years within the slicer should also only show "Monthly" data.
The data is structured in a way that both "Daily" and "Monthly" data are in the same dataset/column differentiated by a seperate column (Type: "Daily", "Monthly"). A calendar table ("CalendarDate") with the transaction date has also been created.
Hope you guys can help me out with this, appreciate it! Thank you in advance!
Well @djwo it sounds like what you need a dynamic temporal scale. I have an example of this coming up in my next book to be published this month.
You will need a measure that changes behavior based on whether you have monthly or daily values available to you. To be more specific, I would need additional information such as sample data and exactly how you want to display the data. Here is a preview of the recipe from my upcoming book if it helps:
To prepare for this recipe, do the following:
R10_Table =
ADDCOLUMNS(
GENERATESERIES(DATE(2016,1,1),TODAY()),
"Inventory",RANDBETWEEN(10000,30000)
)
This table represents inventory amounts per day. With this data, we wish to display average inventory levels summarized by week in the current quarter and by year and quarter for dates that are not in the current quarter.
To implement this recipe, do the following:
IsCurrentQuarter =
VAR __Today = TODAY()
VAR __CurrentYear = YEAR(__Today)
VAR __CurrentQuarter = QUARTER(__Today)
VAR __Year = YEAR('R10_Table'[Value])
VAR __Quarter = QUARTER('R10_Table'[Value])
RETURN
IF(
__Year = __CurrentYear &&
__Quarter = __CurrentQuarter,
TRUE,
FALSE
)
DTS =
IF(
'R10_Table'[IsCurrentQuarter],
"W" & WEEKNUM('R10_Table'[Value]) & " - " & YEAR('R10_Table'[Value]),
"Q" & QUARTER('R10_Table'[Value]) & " - " & YEAR('R10_Table'[Value])
)
DTS Sort By =
IF(
'R10_Table'[IsCurrentQuarter],
YEAR('R10_Table'[Value]) & QUARTER('R10_Table'[Value]) & WEEKNUM('R10_Table'[Value]),
YEAR('R10_Table'[Value]) & QUARTER('R10_Table'[Value])
)
Hi @Greg_Deckler @amitchandak Thank you both for your replies.
@Greg_Deckler, I was hoping the measure would calculate the sum of the sales automatically, depending on today's date following the Logic I mentioned previously. (The data to show, should always be based on today's date)
If today is 2nd March > Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January "Monthly" sales data + Sum of 1st Feburary to 2nd March "Daily" sales data.
If today is 16th March > Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January to Feburary "Monthly" sales data + 1st to 16th March "Daily" sales data.
The data is structured in a way that both "Daily" and "Monthly" data are in the same dataset/column differentiated by a seperate column (Type: "Daily", "Monthly"). A calendar table ("CalendarDate") with the transaction date has also been created.
There can be multiple entries of sales data for a single date. (Transactional Data)
Hey @djwo - So would you say that this is a decent representation of your data?
Date | Sales | Category |
1/1/2020 | 100 | Daily |
1/2/2020 | 50 | Daily |
1/31/2020 | 200 | Daily |
1/31/2020 | 350 | Monthly |
2/1/2020 | 150 | Daily |
2/2/2020 | 45 | Daily |
Like that? Understanding the source data is the most critical component of solving these problems. 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
Hi @Greg_Deckler ,
Yes, that would be a good representation of my data.
However, there can be multiple transaction entries per day along with a customer and salesperson column as well.
(Although the actual table is alot more complicated/messy and contains hundred of thousands of records)
So it would look more like this:
Date | Sales | Type | Product | Salesperson | Customer |
1/1/2020 | 100 | Daily | ProductX | Salesman A | CustomerA |
1/2/2020 | 50 | Daily | ProductX | Salesman A | CustomerA |
1/31/2020 | 200 | Daily | ProductX | Salesman A | CustomerA |
1/31/2020 | 400 | Daily | ProductX | Salesman B | CustomerA |
1/31/2020 | 200 | Monthly | ProductX | Salesman A | CustomerA |
1/31/2020 | 350 | Monthly | ProductX | Salesman A | CustomerA |
2/1/2020 | 150 | Daily | ProductX | Salesman A | CustomerA |
2/2/2020 | 45 | Daily | ProductX | Salesman B | CustomerA |
2/2/2020 | 45 | Monthly | ProductX | Salesman B | CustomerA |
2/2/2020 | 125 | Monthly | ProductX | Salesman A | CustomerA |
A seperate calendar table has also been created:
All the measures and calculations in my report are done using the "DayMonthYear" column.
So what I am hoping to achieve is:
(The data to show, should always be based on today's date)
If today is 2nd March 2020> Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January "Monthly" sales data + Sum of 1st Feburary to 2nd March "Daily" sales data.
If today is 16th March 2020 > Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January to Feburary "Monthly" sales data + 1st to 16th March "Daily" sales data.
If today is 5th Dec 2020 > Selecting the Slicer (Year 2020 Qtr 1) it should show the following > Sum of January to Feburary "Monthly" sales data, as the sales data is already available.
And selecting any time before last month will always yield the sum of the Month sales data.
Thank you again for your help, hope to hear from you.
Thank you for the link to your post on "How to Get Your Question Answered Quickly" it has been very insightful, I'll keep that in mind the next time I post.
@djwo Assuming you have two table. daily and Monthly
Qtd Date =
var _max = maxx(filter('Date','Date'[Date]=TODAY()),'Date'[Date])
var _min = maxx(FILTER('Date','Date'[Date]=_max),STARTOFQUARTER( ('Date'[Date])))
var _max1 = If(day(Today()) >=16 , maxx(filter('Date','Date'[Date]=date(year(TODAY()),month(TODAY())-1,day(TODAY()))),ENDOFMONTH('Date'[Date])),maxx(filter('Date','Date'[Date]=date(year(TODAY()),month(TODAY())-2,day(TODAY()))),ENDOFMONTH('Date'[Date])))
return
CALCULATE(SUM('Sales Monthly'[Sales Amount]),filter(all('Date'),'Date'[Date]<=_max1 && 'Date'[Date]>=_min))
+ CALCULATE(SUM('Sales Daily'[Sales Amount]),filter(all('Date'),'Date'[Date]<_max && 'Date'[Date] >_max1))
// _min & " " & _max & " " & _max1
Hi @amitchandak , thank you for your reponse, but there is a single table that contains both "Daily" and "Monthly" sales transaction data, is there a way we can display the required information by just using a normal Date Slicer?
Is monthly data grouped under some month name or Date(Last or first day of the month) and not repeated as day data.
The first formula should work
Is monthly data grouped under some month name or Date(Last or first day of the month) and repeated as day data.
They add a flag to the calculation
Qtd Date =
var _max = maxx(filter('Date','Date'[Date]=TODAY()),'Date'[Date])
var _min = maxx(FILTER('Date','Date'[Date]=_max),STARTOFQUARTER( ('Date'[Date])))
var _max1 = If(day(Today()) >=16 , maxx(filter('Date','Date'[Date]=date(year(TODAY()),month(TODAY())-1,day(TODAY()))),ENDOFMONTH('Date'[Date])),maxx(filter('Date','Date'[Date]=date(year(TODAY()),month(TODAY())-2,day(TODAY()))),ENDOFMONTH('Date'[Date])))
return
CALCULATE(SUM('Sales'[Sales Amount]),filter(all('Date'),'Date'[Date]<=_max1 && 'Date'[Date]>=_min),'Sales'[Flag]="Month")
+ CALCULATE(SUM('Sales'[Sales Amount]),filter(all('Date'),'Date'[Date]<_max && 'Date'[Date] >_max1),'Sales'[Flag]="Day")
// _min & " " & _max & " " & _max1
Flag is indicative, any field that can do that
If you do not have date form month the create one. All the dates should be in one column and joined to date dim. else share structure
Hi @amitchandak , thank you for help.
What I actually need is a measure to calculate the sum of the Sales data depending of today's date, I am hoping that the date filtering of the date can be hadled by the slicer.
This is what my data looks like:
Date | Sales | Type | Product | Salesperson | Customer |
1/1/2020 | 100 | Daily | ProductX | Salesman A | CustomerA |
1/2/2020 | 50 | Daily | ProductX | Salesman A | CustomerA |
1/31/2020 | 200 | Daily | ProductX | Salesman A | CustomerA |
1/31/2020 | 400 | Daily | ProductX | Salesman B | CustomerA |
1/31/2020 | 200 | Monthly | ProductX | Salesman A | CustomerA |
1/31/2020 | 350 | Monthly | ProductX | Salesman A | CustomerA |
2/1/2020 | 150 | Daily | ProductX | Salesman A | CustomerA |
2/2/2020 | 45 | Daily | ProductX | Salesman B | CustomerA |
2/2/2020 | 45 | Monthly | ProductX | Salesman B | CustomerA |
2/2/2020 | 125 | Monthly | ProductX | Salesman A | CustomerA |
A seperate calendar table has also been created:
All the measures and calculations in my report are done using the "DayMonthYear" column.
So what I am hoping to achieve is:
(The data to show, should always be based on today's date)
If today is 2nd March 2020> Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January "Monthly" sales data + Sum of 1st Feburary to 2nd March "Daily" sales data.
If today is 16th March 2020 > Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January to Feburary "Monthly" sales data + 1st to 16th March "Daily" sales data.
If today is 5th Dec 2020 > Selecting the Slicer (Year 2020 Qtr 1) it should show the following > Sum of January to Feburary "Monthly" sales data, as the sales data is already available.
And selecting any time before last month will always yield the sum of the Month sales data.
Thank you again for your help, looking forward to hearing from you!
It better if can use relative date slicer, all MTD, QTD, YTD, day and yesterday calculation will fall in place.
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
Day Sales =
var _max = maxx('Date','Date'[Date])
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=today()))
Last Day =
var _max = maxx('Date','Date'[Date])
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=today()-1))
Hi @amitchandak, thank you for your reply.
That is the plan, but I am having trouble calculating the sum according to the logic below:
What measure should I use to calculate the sum of the sales depending on Today's date?
(The data to show, should always be based on today's date)
If today is 2nd March 2020> Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January "Monthly" sales data + Sum of 1st Feburary to 2nd March "Daily" sales data.
If today is 16th March 2020 > Selecting the Slicer (Year 2020, Qtr 1) it should show the following > Sum of January to Feburary "Monthly" sales data + 1st to 16th March "Daily" sales data.
If today is 5th Dec 2020 > Selecting the Slicer (Year 2020 Qtr 1) it should show the following > Sum of January to Feburary "Monthly" sales data, as the sales data is already available.
Have you tested this
Qtd Date =
var _max = maxx(filter('Date','Date'[Date]=TODAY()),'Date'[Date])
var _min = maxx(FILTER('Date','Date'[Date]=_max),STARTOFQUARTER( ('Date'[Date])))
var _max1 = If(day(Today()) >=16 , maxx(filter('Date','Date'[Date]=date(year(TODAY()),month(TODAY())-1,day(TODAY()))),ENDOFMONTH('Date'[Date])),maxx(filter('Date','Date'[Date]=date(year(TODAY()),month(TODAY())-2,day(TODAY()))),ENDOFMONTH('Date'[Date])))
return
CALCULATE(SUM('Sales'[Sales]),filter(all('Date'),'Date'[Date]<=_max1 && 'Date'[Date]>=_min),'Sales'[Type]="Monthly")
+ CALCULATE(SUM('Sales'[Sales]),filter(all('Date'),'Date'[Date]<_max && 'Date'[Date] >_max1),'Sales'[Type]="Daily")
// _min & " " & _max & " " & _max1
Have you explored time intelligence functions? Like Dateqtd , totalqtd. If you use date they will take care.
example
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
In case of ytd
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
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/
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |