Creating a complicated measurement of sales based on "Today's date" and "Quarters" (Q!,Q2,Q3,Q4)

[djwo]

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!

[Greg_Deckler]

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:

1. Open Power BI Desktop.
2. Create a table called R10_Table using the following formula:

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.

How to do it...

To implement this recipe, do the following:

1. Create the following columns in table R10_Table:

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])
    )

2. Set the Sort by column for the DTS column in table R10_Table to the DTS Sort By column in table R10_Table.
3. On a report page, create a Clustered column chart visualization and place the column DTS from table R10_Table into the Axis area.
4. For the same clustered column chart, place the column IsCurrentQuarter from table R10_Table into the Legend area.
5. For the same clustered column chart, place the column Inventory from table R10_Table into the Values area and change the aggregation to Average.

[djwo]

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)

[Greg_Deckler]

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

[djwo]

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.

[amitchandak]

@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

[djwo]

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?

[amitchandak]

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

[djwo]

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!

[amitchandak]

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))

[djwo]

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.

[amitchandak]

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

[amitchandak]

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/