Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

  • 1.  DAX for Current week, Last one week, Last two weeks, Last three weeks ,Last four Weeks

    Bronze Contributor
    Posted Jun 29, 2022 12:46 PM

    I have table like this

    Now I want to show current Current week, Last one week, Last two weeks, Last three weeks ,Last four Weeks "ACC_Total". How to write dax for this I'm taking date from Date column



    ------------------------------
    Phaneendra Adusumilli
    ------------------------------


  • 2.  RE: DAX for Current week, Last one week, Last two weeks, Last three weeks ,Last four Weeks

    Posted Jun 30, 2022 02:08 AM
    Hi Phaneendra ,

     I think the best way is to create a calendar table first, where you can define specific columns like current week, last week, ... based on current date.
    Linked to the date of your table you're be able to apply dax measure filtering on calendar columns.

    Create a Date Dimension in Power BI in 4 Steps - Step 1: Calendar Columns - RADACAD

    The advantage of that solution is your are more flexible.

    BR
    David

    ------------------------------
    david Tanchon
    Bi Manager
    ------------------------------

    Original Message


  • 3.  RE: DAX for Current week, Last one week, Last two weeks, Last three weeks ,Last four Weeks

    Bronze Contributor
    Posted Jun 30, 2022 03:32 AM
    Already I have calendar column is there no

    ------------------------------
    Phaneendra Adusumilli
    ------------------------------

    Original Message


  • 4.  RE: DAX for Current week, Last one week, Last two weeks, Last three weeks ,Last four Weeks

    Bronze Contributor
    Posted Jun 30, 2022 09:08 AM
    Hi Phaneendra, the way that I would tackle that problem is to drive this from your 'Dates' table with a Dates[RelativeWeek] column where 0 is this week, -1 is last week, 1 is next week and so on.  It is then very simple to add Dates[RelativeWeek] as a filter condition in a CALCULATE function.

    ------------------------------
    Hugh Johnson
    Dublin, Ireland
    ------------------------------

    Original Message


  • 5.  RE: DAX for Current week, Last one week, Last two weeks, Last three weeks ,Last four Weeks

    Posted Jul 04, 2022 03:52 PM

    Agree with @Hugh Johnson ...
    Setup up your calendar with 'Offset' columns. I always include Days offset, Weeks Offset, Months Offset, and Years Offset.​ Makes it easy then to specify exactly what time-period you want to calculate in the measure.

    here are a few DAX examples:
    - Sales total for 2 years ago, 

    Sales -2Yr = CALCULATE (SUM ( 'Sales'[Sales] ),'Date'[yrOffset] = -2)

    - Same Month, 2 years ago 

    Sales Same Month -2yrs = CALCULATE (SUM ( 'Sales'[Sales] ),'Date'[monthOffset] = -24)

    - Sales 17 weeks ago

    Sales 17 weeks ago = CALCULATE (SUM ( 'Sales'[Sales] ),'Date'[weekOffset] = -17)

    ... etc

    you get the idea - extremely flexible especially with weeks which is not included in the time intelligence calculations.



    ------------------------------
    Bob Parsons
    Business Intelligence Manager
    Star Produce
    ------------------------------

    Original Message