measure ignoring hierarchy

[francoisl]

Hi all, 

I guess I still have not understood the all and allexcept and hopefully my explanation of the problem will be clear. 

Here's my problem: 

Date table with the following : 

date                          fiscal_year             fiscal_period         fiscal_week 

jan 01 2020               2020                      1                          1

..... and so on 

jan 01 2021               2021                       1                         1

.... and so on 

Sales Table :

jan 01 2020                  20$

jan 01 2020                  40$

jan 01 2021                  50$

jan 01 2021                  60$ 

My matrix has fiscal week and date in the rows. 

I have a slicer with Fiscal Year and period. 

The fiscal period and week are never in sync from one year to another. 

So: 

I have a simple measure to calculate the current date sales. 

However my user wants the following : 

1) total of last year for the period/week on the total line only of the week. 

simple sum of sales is    sales = calculate(sum(sales))    -- pretty simple and it bends to the slicer and hierarchy. 

I want to cumulate sales   the following way (

 where

            current period = the current period in the hierarchy   --  Current value in expression = 4

            current week    = the current week in the hierarchy     -- Current value in expression = 1,2,3,4 depending on the line

            LastFiscYear      = current fiscal year selected -1           -- Current value in expression = 2020. 

LYSales = calculate(sum(sales),FILTER(DIMENSION_CALENDAR,DIMENSION_CALENDAR[FISCAL_PERIOD]=[CurrentPeriod]&&DIMENSION_CALENDAR[FISCAL_WEEK]=[CurrentWeek]&&DIMENSION_CALENDAR[FISCAL_YEAR]=[LastFiscYear]))

My problem is that I cannot get the total of last year by period/week but for last year. 

Any clues would be appreciated.  

[v-jingzhang · Accepted solution]

Hi @francoisl 

In your scenario, if you want to get the total of sales in a period/week last year, you can try below measure. Add ALL to the DateTable.

LYSales =
CALCULATE (
    SUM ( 'SalesTable'[sales] ),
    FILTER (
        ALL ( 'DateTable' ),
        'DateTable'[FISCAL_PERIOD] = [CurrentPeriod]
            && 'DateTable'[FISCAL_WEEK] = [CurrentWeek]
            && 'DateTable'[FISCAL_YEAR] = [LastFiscYear]
    )
)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[francoisl]

Hi, 

Sorry all for the late response.  This is exactly what I did and thanks for this solution. 

Also, on another note I created a column in my date table that would tell me if the date is before or after the current date. Why? because as the period progress in sales numbers I do not want to show future date. 

I just filtred that in my table. 

Thanks again

[amitchandak]

@francoisl , I have blogs and video on how to deal with week, WOW , week year on year

Date table need new columns

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

refer

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8