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

 

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