Forum Discussion

gavin007's avatar
gavin007
Helper V
4 years ago
Solved

Use calculate to filter data based on date column

Hi all, it sounds a simple task but I must have done something wrong. I have a simple fact table with transaction volume and batch date column as date type. When I use the following dax, the result i...
Need Help
  • m3tr01d's avatar
    m3tr01d
    4 years ago

    gavin007 
    In your pbix, the way you calculate the Current week gives Week Start date = 2021/11/29.
    It seems to me that the current week should start on 2021/11/22 based on your calendar table?

    The table Fact COmbined Agg doesn't have any data for 2021. Its kinda difficult for us to help you.

    You can use a variable to find what is the current week based on Today's date, then calculate the Volume for this week.

    Volume_Current =
VAR _CurrentWeek = 
    CALCULATE (
        SELECTEDVALUE ( 'Actual Date'[Start Date of Week] ),
        REMOVEFILTERS ( 'Actual Date' ),
        'Actual Date'[Date] = TODAY ()
    )
RETURN
CALCULATE(
	SUM( 'Facts Combined_Agg'[Transaction Volume Amount] ),
	'Actual Date'[Start Date of Week] = _CurrentWeek,
	REMOVEFILTERS( 'Actual Date' )
)


    If you want to week before the Current one, you can substract 7 days

    Volume_Current =
VAR _CurrentWeek = 
    CALCULATE (
        SELECTEDVALUE ( 'Actual Date'[Start Date of Week] ),
        REMOVEFILTERS ( 'Actual Date' ),
        'Actual Date'[Date] = TODAY ()
    )
RETURN
CALCULATE(
	SUM( 'Facts Combined_Agg'[Transaction Volume Amount] ),
	'Actual Date'[Start Date of Week] = _CurrentWeek - 7,   --Substract 7 days for previous
	REMOVEFILTERS( 'Actual Date' )
)
Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Ideally you should have a Calendar Table with a relationship to the Batch Date table.  In the slicer drag date from the Calendar table and select any date.  Just use this measure

Measure = sum('Facts Combined_Agg'[Transaction Volume Amount])

  • gavin007's avatar
    gavin007
    Helper V
    4 years ago

    What i want to achieve is using a measure to sum all sales  that related to current week, last week, week minus 2, week minus 3. 

     

    volume current week = CALCULATE (
    SUM ( 'Facts Combined_Agg'[Transaction Volume Amount] ),
    'Actual Date'[Start Date of Week] = max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1)

     


    I am able to work out the week range selection like below. But when i apply back to the above measure, I can't embed in it properly.

     

    Current Week= max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1

    Last Week= max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1-7

    Week Minus 2= max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1-14

    Week Minus 3= max('Actual Date'[date])-weekday(max('Actual Date'[date]),2)+1-21

     

    I upload the pbix file for play. https://1drv.ms/u/s!Aig3EWdV94jKgpsXaMC8AAp5TMW0UA?e=ROWk5D 

     

    There are two tables-date and Fact Combined_Agg. I build relationship between them, 

    • m3tr01d's avatar
      m3tr01d
      Continued Contributor
      4 years ago

      gavin007 
      In your pbix, the way you calculate the Current week gives Week Start date = 2021/11/29.
      It seems to me that the current week should start on 2021/11/22 based on your calendar table?

      The table Fact COmbined Agg doesn't have any data for 2021. Its kinda difficult for us to help you.

      You can use a variable to find what is the current week based on Today's date, then calculate the Volume for this week.

      Volume_Current =
VAR _CurrentWeek = 
    CALCULATE (
        SELECTEDVALUE ( 'Actual Date'[Start Date of Week] ),
        REMOVEFILTERS ( 'Actual Date' ),
        'Actual Date'[Date] = TODAY ()
    )
RETURN
CALCULATE(
	SUM( 'Facts Combined_Agg'[Transaction Volume Amount] ),
	'Actual Date'[Start Date of Week] = _CurrentWeek,
	REMOVEFILTERS( 'Actual Date' )
)


      If you want to week before the Current one, you can substract 7 days

      Volume_Current =
VAR _CurrentWeek = 
    CALCULATE (
        SELECTEDVALUE ( 'Actual Date'[Start Date of Week] ),
        REMOVEFILTERS ( 'Actual Date' ),
        'Actual Date'[Date] = TODAY ()
    )
RETURN
CALCULATE(
	SUM( 'Facts Combined_Agg'[Transaction Volume Amount] ),
	'Actual Date'[Start Date of Week] = _CurrentWeek - 7,   --Substract 7 days for previous
	REMOVEFILTERS( 'Actual Date' )
)
      • gavin007's avatar
        gavin007
        Helper V
        4 years ago

        I don't know selectvalue cam be use like this in calculate dax. 

         

        • Let me try it tomorrow. Thanks mate. 
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      In the Calendar Table, do you have a week number column?