Power BI Desktop (DirectQuery) Sum Measure Based on Dynamic Dates

[Anonymous]

Hi DAX Experts,

Hope you could help me again or hear your suggestions (I am still learning PowerBI).

Using DirectQuery, how to get the sum of a measure based on two dynamic dates like getting the month's, MTD or YTD as of the Business Date?

To make the sample look simpler, let's use the current month (but I am really looking at last year's sales), I've got a table of invoiced sales with invoiced date and sales measure.  The second table contains my date dimension with flags to identify the MTD, YTD, current or previous business month and business date (thanks to this forum).

Business Day = CALCULATE( FirstNonBlank(DateTable[DateField], DateTable[DateField]),Filter(DateTable, DateTable[BusinessDayFlagField] = 1)) 

This works to get the whole month's sales (utilizing SalesTable and also DateTable):

MonthSales= CALCULATE( SUMX(SalesTable, SalesTable[Sales Value] ), 'DateTable'[CurrentMonthFlagField] = TRUE ) 

Is there a way to just grab the sales for the current month as of the business date (not the whole month)?  

These formulas didn't work:

CurrentSales = CALCULATE( SUM('SalesTable'[Sales Value]), SalesTable[Invoice Date] >= Datevalue([Business Day]))     --> error: A function 'CALCULATE' has been used in a True/False expression...

Using two filters (less than and equal to the business date but within the current month)

CurrentSales2 = CALCULATETABLE( SUMMARIZE(SalesTable, "Month Sales", SUM(SalesTable[Sales Value])) , SalesTable[DateField] <= Datevalue([Business Day]), DataTable[CurrentMonthFlagField] = TRUE)     --> wrong syntax

Cheers,

- Louis

[v-shex-msft]

Hi @Anonymous,

What type of data you stored in business day column? If it contains whole number, I don't think datevalue can convert them to date formula.

Can you please share more detail content and some sample data/pbix file for test?

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks Xiaoxin for your reply.

The business day is a date field which is based on my date table (based on the current business day flag which gets updated daily).

Our financial year starts on June to July.  I've also higlighted the Date Table for the current day and month.

May I ask how do you exchange files in this forum?  Or should I just get your email for the sample data? 

Cheers,

- Louis

[v-shex-msft]

Hi @Anonymous,

Maybe you can try to use below formula, I modify your formula and move your condition into filter function:

CurrentSales =
CALCULATE (
    SUM ( 'SalesTable'[Sales Value] ),
    FILTER (
        ALLSELECTED ( SalesTable ),
        SalesTable[Invoice Date] >= DATEVALUE ( [Business Day] )
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

Kindly excuse the delay.  I've been trying to work out your suggestion but I can't make it work.  Maybe I need two filters?

Like the limit of the whole month until the business day?

For example: SalesTable[Invoice Date] <= DATEVALUE ( [Business Day] ) AND DateTable([CurrentMonthFlag = 1])

I could send you a sample file .pbix file if you'd require.

Warm regards,

- Louis

[v-shex-msft]

HI @Anonymous,

Ok, you can share the pbix file for some further testing.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

How can I share files in this forum?

Cheers,

- Louis

[v-shex-msft]

Hi @Anonymous,

If your data not contains any sensitive data, you can direct share it.

Otherwise please do mask on these data before share or create some fake data with similar table structure.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

Here's a sample of budget (I've used budget now). 

For example, the current date is 19 January 2018 (and current month is January).

MTDBudget = CALCULATE( SUMX(YTDBudget, YTDBudget[Sales Budget] ), 'YTDBudget'[CurrentMonthFlag] = 1 ) 

Business Day = CALCULATE( FirstNonBlank(YTDBudget[Budget Date], YTDBudget[Budget Date]),Filter(YTDBudget, YTDBudget[CurrentDayFlag] = 1))

The question is how to only get the Current Budget based on the Business Day? (like the budget within the current month but less than or equal to the Business Day)

Just a gentle rmeinder, I am using DirectQuery.

Thanks again afor your time, Xiaoxin.

Cheers,

- Louis

 BudgetDate FinMonth FinYear CurDayFlag CurMonFlag SalesBudget MarginBudget
1/07/2017 0:00 1 2018 0 0 362 130
2/07/2017 0:00 1 2018 0 0 362 130
5/07/2017 0:00 1 2018 0 0 362 130
6/07/2017 0:00 1 2018 0 0 362 130
7/07/2017 0:00 1 2018 0 0 362 130
8/07/2017 0:00 1 2018 0 0 362 130
9/07/2017 0:00 1 2018 0 0 362 130
12/07/2017 0:00 1 2018 0 0 362 130
13/07/2017 0:00 1 2018 0 0 362 130
14/07/2017 0:00 1 2018 0 0 362 130
15/07/2017 0:00 1 2018 0 0 362 130
16/07/2017 0:00 1 2018 0 0 362 130
19/07/2017 0:00 1 2018 0 0 362 130
20/07/2017 0:00 1 2018 0 0 362 130
21/07/2017 0:00 1 2018 0 0 362 130
22/07/2017 0:00 1 2018 0 0 362 130
23/07/2017 0:00 1 2018 0 0 362 130
26/07/2017 0:00 1 2018 0 0 362 130
27/07/2017 0:00 1 2018 0 0 362 130
28/07/2017 0:00 1 2018 0 0 362 130
29/07/2017 0:00 1 2018 0 0 362 130
30/07/2017 0:00 1 2018 0 0 362 130
2/08/2017 0:00 2 2018 0 0 352 126
3/08/2017 0:00 2 2018 0 0 352 126
4/08/2017 0:00 2 2018 0 0 352 126
5/08/2017 0:00 2 2018 0 0 352 126
6/08/2017 0:00 2 2018 0 0 352 126
9/08/2017 0:00 2 2018 0 0 352 126
10/08/2017 0:00 2 2018 0 0 352 126
11/08/2017 0:00 2 2018 0 0 352 126
12/08/2017 0:00 2 2018 0 0 352 126
13/08/2017 0:00 2 2018 0 0 352 126
16/08/2017 0:00 2 2018 0 0 352 126
17/08/2017 0:00 2 2018 0 0 352 126
18/08/2017 0:00 2 2018 0 0 352 126
19/08/2017 0:00 2 2018 0 0 352 126
20/08/2017 0:00 2 2018 0 0 352 126
23/08/2017 0:00 2 2018 0 0 352 126
24/08/2017 0:00 2 2018 0 0 352 126
25/08/2017 0:00 2 2018 0 0 352 126
26/08/2017 0:00 2 2018 0 0 352 126
27/08/2017 0:00 2 2018 0 0 352 126
30/08/2017 0:00 2 2018 0 0 352 126
31/08/2017 0:00 2 2018 0 0 352 126
1/09/2017 0:00 3 2018 0 0 379 135
2/09/2017 0:00 3 2018 0 0 379 135
3/09/2017 0:00 3 2018 0 0 379 135
6/09/2017 0:00 3 2018 0 0 379 135
7/09/2017 0:00 3 2018 0 0 379 135
8/09/2017 0:00 3 2018 0 0 379 135
9/09/2017 0:00 3 2018 0 0 379 135
10/09/2017 0:00 3 2018 0 0 379 135
13/09/2017 0:00 3 2018 0 0 379 135
14/09/2017 0:00 3 2018 0 0 379 135
15/09/2017 0:00 3 2018 0 0 379 135
16/09/2017 0:00 3 2018 0 0 379 135
17/09/2017 0:00 3 2018 0 0 379 135
20/09/2017 0:00 3 2018 0 0 379 135
21/09/2017 0:00 3 2018 0 0 379 135
22/09/2017 0:00 3 2018 0 0 379 135
23/09/2017 0:00 3 2018 0 0 379 135
24/09/2017 0:00 3 2018 0 0 379 135
27/09/2017 0:00 3 2018 0 0 379 135
28/09/2017 0:00 3 2018 0 0 379 135
29/09/2017 0:00 3 2018 0 0 379 135
30/09/2017 0:00 3 2018 0 0 379 135
1/10/2017 0:00 4 2018 0 0 439 156
4/10/2017 0:00 4 2018 0 0 439 156
5/10/2017 0:00 4 2018 0 0 439 156
6/10/2017 0:00 4 2018 0 0 439 156
7/10/2017 0:00 4 2018 0 0 439 156
8/10/2017 0:00 4 2018 0 0 439 156
11/10/2017 0:00 4 2018 0 0 439 156
12/10/2017 0:00 4 2018 0 0 439 156
13/10/2017 0:00 4 2018 0 0 439 156
14/10/2017 0:00 4 2018 0 0 439 156
15/10/2017 0:00 4 2018 0 0 439 156
18/10/2017 0:00 4 2018 0 0 439 156
19/10/2017 0:00 4 2018 0 0 439 156
20/10/2017 0:00 4 2018 0 0 439 156
21/10/2017 0:00 4 2018 0 0 439 156
22/10/2017 0:00 4 2018 0 0 439 156
25/10/2017 0:00 4 2018 0 0 439 156
26/10/2017 0:00 4 2018 0 0 439 156
27/10/2017 0:00 4 2018 0 0 439 156
28/10/2017 0:00 4 2018 0 0 439 156
29/10/2017 0:00 4 2018 0 0 439 156
1/11/2017 0:00 5 2018 0 0 429 154
2/11/2017 0:00 5 2018 0 0 429 154
3/11/2017 0:00 5 2018 0 0 429 154
4/11/2017 0:00 5 2018 0 0 429 154
5/11/2017 0:00 5 2018 0 0 429 154
8/11/2017 0:00 5 2018 0 0 429 154
9/11/2017 0:00 5 2018 0 0 429 154
10/11/2017 0:00 5 2018 0 0 429 154
11/11/2017 0:00 5 2018 0 0 429 154
12/11/2017 0:00 5 2018 0 0 429 154
15/11/2017 0:00 5 2018 0 0 429 154
16/11/2017 0:00 5 2018 0 0 429 154
17/11/2017 0:00 5 2018 0 0 429 154
18/11/2017 0:00 5 2018 0 0 429 154
19/11/2017 0:00 5 2018 0 0 429 154
22/11/2017 0:00 5 2018 0 0 429 154
23/11/2017 0:00 5 2018 0 0 429 154
24/11/2017 0:00 5 2018 0 0 429 154
25/11/2017 0:00 5 2018 0 0 429 154
26/11/2017 0:00 5 2018 0 0 429 154
29/11/2017 0:00 5 2018 0 0 429 154
30/11/2017 0:00 5 2018 0 0 429 154
1/12/2017 0:00 6 2018 0 0 436 160
2/12/2017 0:00 6 2018 0 0 436 160
3/12/2017 0:00 6 2018 0 0 436 160
6/12/2017 0:00 6 2018 0 0 436 160
7/12/2017 0:00 6 2018 0 0 436 160
8/12/2017 0:00 6 2018 0 0 436 160
9/12/2017 0:00 6 2018 0 0 436 160
10/12/2017 0:00 6 2018 0 0 436 160
13/12/2017 0:00 6 2018 0 0 436 160
14/12/2017 0:00 6 2018 0 0 436 160
15/12/2017 0:00 6 2018 0 0 436 160
16/12/2017 0:00 6 2018 0 0 436 160
17/12/2017 0:00 6 2018 0 0 436 160
20/12/2017 0:00 6 2018 0 0 436 160
21/12/2017 0:00 6 2018 0 0 436 160
22/12/2017 0:00 6 2018 0 0 436 160
23/12/2017 0:00 6 2018 0 0 436 160
24/12/2017 0:00 6 2018 0 0 436 160
27/12/2017 0:00 6 2018 0 0 436 160
28/12/2017 0:00 6 2018 0 0 436 160
29/12/2017 0:00 6 2018 0 0 436 160
30/12/2017 0:00 6 2018 0 0 436 160
31/12/2017 0:00 6 2018 0 0 436 160
3/01/2018 0:00 7 2018 0 1 276 101
4/01/2018 0:00 7 2018 0 1 276 101
5/01/2018 0:00 7 2018 0 1 276 101
6/01/2018 0:00 7 2018 0 1 276 101
7/01/2018 0:00 7 2018 0 1 276 101
10/01/2018 0:00 7 2018 0 1 276 101
11/01/2018 0:00 7 2018 0 1 276 101
12/01/2018 0:00 7 2018 0 1 276 101
13/01/2018 0:00 7 2018 0 1 276 101
14/01/2018 0:00 7 2018 0 1 276 101
17/01/2018 0:00 7 2018 0 1 276 101
18/01/2018 0:00 7 2018 0 1 276 101
19/01/2018 0:00 7 2018 1 1 276 101
20/01/2018 0:00 7 2018 0 1 276 101
21/01/2018 0:00 7 2018 0 1 276 101
24/01/2018 0:00 7 2018 0 1 276 101
25/01/2018 0:00 7 2018 0 1 276 101
26/01/2018 0:00 7 2018 0 1 276 101
27/01/2018 0:00 7 2018 0 1 276 101
28/01/2018 0:00 7 2018 0 1 276 101
31/01/2018 0:00 7 2018 0 1 276 101

[v-shex-msft]

Hi @Anonymous,

I modified your formula, current it can works on correct date range:

MTDBudget =
IF (
    MAX ( YTDBudget[CurMonFlag] ) = 1,
    CALCULATE (
        SUM ( YTDBudget[SalesBudget] ),
        FILTER ( ALLSELECTED ( YTDBudget ), 'YTDBudget'[CurMonFlag] = 1 )
    )
)

Regards,
Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin.

Yes, I am able to get the Budget for the whole month.

MonthBudget = CALCULATE( SUMX(YTDBudget, YTDBudget[Sales Budget] ), 'YTDBudget'[CurrentMonthFlag] = 1 ) 

But is it possible to get the Current Budget as of Business Day?

Like in my copy-pasted table (you can copy-paste it to Excel: Text to Column > Delimited (Other) - by copying and pasting the space between the "BudgetDate" and "FinMonth".

The file should look like this.  The current day flag (CurDayFlag) should fall on 19 Jan. 2018 (and the current month flag CurMonFlag should be all January dates).

The Month Budget is 5,796 (the budget for the whole month of January).

But I need to only get 3,588 (the budget from 3 January to 19 January).

Cheers,

- Louis

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>>But I need to only get 3,588 (the budget from 3 January to 19 January).

In my opinion, you can use 'lookupvalue' function to find out current date, then use it as condition to filter on calculate formula.

MTDBudget = 
 IF(MAX(YTDBudget[CurMonFlag])= 1,CALCULATE( SUM(YTDBudget[SalesBudget] ),FILTER(ALLSELECTED(YTDBudget),'YTDBudget'[CurMonFlag] = 1 && [BudgetDate] <=LOOKUPVALUE(YTDBudget[BudgetDate],YTDBudget[CurDayFlag],1)  )))

Regards,

Xiaoxin Sheng