cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
francoisl
Helper I
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. 

 

francoisl_0-1619549210896.png

 

 

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
v-jingzhang
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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.

View solution in original post

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

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

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