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

 

act gtv current week = calculate(SUM('Facts Combined_Agg'[Transaction Volume Amount]),'Facts Combined_Agg'[Batch Date]=20/10/2021)

 

but when I change the "20/10/2021" to 44481 which is integer, it works. 

 

My question is--the batch date column is date type, not general number type, but why the formula require number type to work. Am I missing something? 

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

6 Replies

  • 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' )
)