Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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