Forum Discussion

AltGr9's avatar
AltGr9
Helper I
5 years ago
Solved

show week by date selection

Hi all,

 

This is what I'm tyring to get to:

 

 

 

 

 

 

 

 

I have tried a DAX measure that puts the max date in a variable (to get the slicer's date) and then in another variable the date seven days prior to that (using a an index on the date table minus 7), and returns a calculation that takes my Actuals between those two dates:

 

Last 7 days) =

VAR FilteredDate = MAX('Dimension Date'[Date])

VAR DateIncrementMinus7 = CALCULATE(MAX('Dimension Date'[Daily Increment]), ALL('Dimension Date') ,'Dimension Date'[Date] = FilteredDate) - 7

VAR FilteredDateMinus7 = CALCULATE(MAX('Dimension Date'[Date]), ALL('Dimension Date') ,'Dimension Date'[Daily Increment] = DateIncrementMinus7)

RETURN

CALCULATE([Actual], 'Dimension Date'[Date] <= FilteredDate, 'Dimension Date'[Date] >= FilteredDateMinus7, ALL('Dimension Date'))

 

But this is not giving me what I want at all. Thanks in advance for your help!

Need Help
  • AltGr9's avatar
    AltGr9
    5 years ago

    Hi, ok, I've found a solution to my problem.

     

    Here it is for anyone struggling with the same issue:

     

    1. I already had a Date Dimension table related to my Fact table, so I Duplicated it to create two Date Dimension tables
    2. On the Date Duplicate table, I ensured there were no relationships between this table and my fact table
    3. I created a date slicer based on the Date column of the Date Duplicate table, setting the slicer to Before
    4. In my Fact table I created a new calculated column (not measure) to bring over the index that I had on my Date table, as follows:
      • Date Index = MAXX( RELATEDTABLE('Dimension Date'), 'Dimension Date'[Index] )
    5. I created a duplicate date column in my original Date table and formatted it to Data type Date, and Format dddd in the Column tools part of the ribbon:
      • Day = 'Dimension Date'[Date]
    6. I created the following measure in my Fact table, ensuring I set the data type to Decimal and the Format to decimal with 2 decimal places in the measure tools ribbon:
      • Actual Measure =
        VAR _SelectedValue = MAX('Date Duplicate'[Date])
        VAR _DateIndex = CALCULATE( MAX('Date Duplicate'[Index]), 'Date Duplicate'[Date] = _SelectedValue )
        VAR _DateIndexMinus7 = _DateIndex -7
        VAR _MaxIncrement = MAX('Fact'[Date Index])
        VAR _MaxActual = MAX('Fact'[Actual])

        RETURN
        IF(AND(_MaxIncrement <= _DateIndex, _MaxIncrement > _DateIndexMinus7), _MaxActual, BLANK())
    7. I created a Clustered Column chart Visual setting the Axis to my new Day column (created above) and the Values to the Actual Measure in the Fact table
    8. I set the Day column in the Visual to Day not Date Hierarchy
    9. I set the X Axis Type to Categorical
    10. I removed the X and Y axis titles.
    11. Done.
     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    AltGr9 , Try like

    Last 7 days =
    VAR FilteredDate = MAXX(allselected('Dimension Date') ,'Dimension Date' [Date])
    VAR DateIncrementMinus7 = FilteredDate - 7
    RETURN
    CALCULATE([Actual], filter( ALL('Dimension Date'),'Dimension Date'[Date] <= FilteredDate, 'Dimension Date'[Date] >= DateIncrementMinus7))

     

    or

     

    Last 7 days =
    VAR FilteredDate = MAXX(allselected('Dimension Date') ,'Dimension Date' [Date])
    VAR DateIncrementMinus7 = FilteredDate - 7
    RETURN
    CALCULATE([Actual], filter( ('Dimension Date'),'Dimension Date'[Date] <= FilteredDate, 'Dimension Date'[Date] >= DateIncrementMinus7))

     

    If there is order issue with weekday name the use sort column

    https://radacad.com/sort-a-column-with-a-custom-order-in-power-bi

     

     

    Also check this approch

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

  • AltGr9's avatar
    AltGr9
    Helper I
    5 years ago

    Hi, ok, I've found a solution to my problem.

     

    Here it is for anyone struggling with the same issue:

     

    1. I already had a Date Dimension table related to my Fact table, so I Duplicated it to create two Date Dimension tables
    2. On the Date Duplicate table, I ensured there were no relationships between this table and my fact table
    3. I created a date slicer based on the Date column of the Date Duplicate table, setting the slicer to Before
    4. In my Fact table I created a new calculated column (not measure) to bring over the index that I had on my Date table, as follows:
      • Date Index = MAXX( RELATEDTABLE('Dimension Date'), 'Dimension Date'[Index] )
    5. I created a duplicate date column in my original Date table and formatted it to Data type Date, and Format dddd in the Column tools part of the ribbon:
      • Day = 'Dimension Date'[Date]
    6. I created the following measure in my Fact table, ensuring I set the data type to Decimal and the Format to decimal with 2 decimal places in the measure tools ribbon:
      • Actual Measure =
        VAR _SelectedValue = MAX('Date Duplicate'[Date])
        VAR _DateIndex = CALCULATE( MAX('Date Duplicate'[Index]), 'Date Duplicate'[Date] = _SelectedValue )
        VAR _DateIndexMinus7 = _DateIndex -7
        VAR _MaxIncrement = MAX('Fact'[Date Index])
        VAR _MaxActual = MAX('Fact'[Actual])

        RETURN
        IF(AND(_MaxIncrement <= _DateIndex, _MaxIncrement > _DateIndexMinus7), _MaxActual, BLANK())
    7. I created a Clustered Column chart Visual setting the Axis to my new Day column (created above) and the Values to the Actual Measure in the Fact table
    8. I set the Day column in the Visual to Day not Date Hierarchy
    9. I set the X Axis Type to Categorical
    10. I removed the X and Y axis titles.
    11. Done.