show week by date selection

[AltGr9]

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!

[AltGr9 · Accepted solution]

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.

[amitchandak]

@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