cancel
Showing results for
Did you mean:  Helper I

## measure ignoring hierarchy

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.
1 ACCEPTED SOLUTION  Community Support

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.

3 REPLIES 3  Community Support

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.  Helper I

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  Super User IV

@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

Proud to be a Super User! Announcements #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals. Top Solution Authors
Top Kudoed Authors
Users online (3,333)