Forum Discussion

notrop8's avatar
notrop8
Frequent Visitor
3 years ago
Solved

Sum value for three month based on slicer

Dear Experts,   I have a sales table with 'project_name', 'country', 'value', 'start_date', and 'due_date' (notably).  I have several slicers, such as 'calendar(date)' and 'country'.   I want to...
Need Help
notrop8's avatar
notrop8
Frequent Visitor
3 years ago

It is not fixing, with "SELECTEDVALUES" nothing is selected.

 

Here is a simmplified pbix file, with the xlsx data source for 'Sales' table : download link (I can not attach file on the forum)

As you can see, only the first occurence is selected (for Canada, only the first row for 1st January 2014 is selected, wheras I want all rows).

 

Many Thanks

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi notrop8 ,

 

This link shows "The transfer you requested has been deleted".Please provide a valid pbix file.

 

You can place the 'Calendar'[Date] field on the slicer and select it, then create a separate measure Measure=SELECTEDVALUE('Calendar'[Date]) and see his result, if he is invalid please try

 

Measure M to M+2 Value = 
VAR selected_date = Min('Calendar'[Date])
RETURN 
CALCULATE(
    SUM('sales'[value]),
            FILTER(ALL('sales'),
           'sales'[country]=MAX('sales'[country])&&   'sales'[due_date] >= selected_date && 'sales'[due_date] <= EOMONTH(selected_date , 2)))

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

 

  • notrop8's avatar
    notrop8
    Frequent Visitor
    3 years ago

    Thank you for your assistance Anonymous , but I get this error 'The MAX function only accepts a column reference as an argument.'. And yes, Measure=SELECTEDVALUE('Calendar'[Date]) returns nothing.

     

    Please find here or here or here the example files.