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
