Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX for Measure/Calculated Column to identify time period selection slicer

Hi All, I have one fact table (Sales) and one dimension Tables (dimTime) Fact table structure: dimTime Structure: Join is one to Many and primary key is 'Date' column Requirement...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _select=SELECTEDVALUE('Sales'[Market])
var _maxdate=MAXX(FILTER(ALL(Sales),'Sales'[Market]=_select),[Date])
return
IF(
MAX('Sales'[Date]) > EOMONTH(_maxdate,-4) &&MAX('Sales'[Date])<=_maxdate,1,0)

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Result:

    When no slicer is selected, a 3-month interval is displayed for each market

    Select slicers to display the interval for the specified Market for 3 months

     

     

    Best Regards,

    Liu Yang

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , based on what I got. If I have country and Date dimension and I create a measure like

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))

 

then it should take max country date when we display it by country

 

 

or try like

Rolling 6 =

Var _max = maxx(filter(allselected(Sales), [Country] = max(Sales[Country])) , Sales[Sales date])

return

CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],_amx,-6,MONTH))

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi amitchandak 

 

I do not need Sales Amount Calculation, rather 3 and 6 month time period selection which will be dependent on Market selection.