Forum Discussion

francoisl's avatar
francoisl
Icon for Helper II rankHelper II
5 years ago
Solved

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     ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
francoisl
Icon for Helper II rankHelper II
5 years ago

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