Calculate sales (sum of a column) based on the date selected using filter.

[Anonymous]

I am having a date filter like shown above where the user can select a year (where all months of the year gets selected) or the user can select a specific month (and all days of the month gets selected) or select a specific day. If the user selects a year, the total sales of the entire year is to calculated. If the user selects a month, need to calculate sales of the month only and if the user selects a day, MTD sales is to be calculated. Any ideas for a DAX function to handle this?

[Dangar332]

hi, @Anonymous 

try below measure

measure =
if(
  isinscope(datetable[day column]),
  calculate(
    sum(tablename[sales]),
    removefilters(),
    values(datetable[day column]),
    datesmtd(datetable[day column])
  ),
 sum(tablename[sales])
) 

[Anonymous]

Hi @Dangar332,
Thanks for your response. When I tried the measure you provided, its returning an error - 

A column specified in the call to function 'DATESMTD'  is not of type DATE. 
So I tried the Date column (and not day coumn), the else part was working fine but the if part is returning the daywise sales and not MTD. 

measure = 
if(
  isinscope('Date Master'[Day]),
  calculate(
    [Sales Value],
    removefilters(),
    values('Date Master'[Day]),
    datesmtd('Date Master'[Date])
  ),
 [Sales Value])

 

My Date Master has columns:

1. Date
2. Day - (Measure= DAY('Date Master'[Date])
3. Month -
4. Year -
   
   I have used Year, Month and Day as fields in slicer.  

[Dangar332]

hi, @Anonymous 

remove 

values('Date Master'[Day])

from code

[Anonymous]

Hi @Dangar332 

Still the problem persist. Ay other ideas to solve the problem?

[Dangar332 · Accepted solution]

hi, @Anonymous 

try below measure and see below image it might help

Measure 2 = 
var a  = HASONEFILTER('Calendar'[Date].[Day])
return
IF(a,CALCULATE([Quantity],DATESMTD('Calendar'[Date])),[Quantity])

for month selection

for day selection