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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        HI Anonymous,

         

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

         

        Regards,

        Xiaoxin Sheng