Compare services of last year/month/week with current year/month/week

[helalmob]

One report/dashboard that I did in Tableau for my company was to compare services this year, month, and week with last year, month, and week. Another words, report has Year, Month, and Week filters and when staff chose let’s say 2018, Feb, Week 1 the report populate data and graphs (HeatMap) of 2018 Feb Week 1 with 2017 Feb Week 1. My question is how can I calculate last year month and week services in Dax/POwer BI? I used Sameperiodlastyear but that only gave me this year as compared to last year and no more.  Here is Tableau snapshot. Actual in this report refers to current year/month/week and budget referes to last year. THe KPI indicates that if this year/month/week services are lower than or equal to same period of last year then the color is Red and if is greater tha last year is Green. Thank you

[v-xjiin-msft]

Hi @helalmob,

Since you didn't share us your source tables structure and some sample data. I assumed they looks like this:

Then to get the total value for this year for each UnitID group. You can try following measure:

This Year =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ) ),
    ALLEXCEPT ( 'Table', 'Table'[UnitID] )
)

Or:

This Year =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) ) ),
    ALLEXCEPT ( 'Table', 'Table'[UnitID] )
)

Last Year is like:

Last Year =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) ) - 1 ),
    ALLEXCEPT ( 'Table', 'Table'[UnitID] )
)

If you have month slicer to filter data based on month. The month measure may be like this:

This month =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        MONTH ( 'Table'[Date] ) = SELECTEDVALUE ( 'Table'[Month] )
            && YEAR ( 'Table'[Date] ) = YEAR ( TODAY () )
    ),
    ALLEXCEPT ( 'Table', 'Table'[UnitID] )
)

If above sample doesn't satisfy your requirement, please share us more detailed information like your source table structure and some sample data. Also your desired result based on your sample data will help us get a right direction. Please attach it too.

Thanks,
Xi Jin.

[helalmob]

Hi Xi Jin,

Thank you so much for the help. Althouh your DAX formula helped a lot but you were right, I didn’t provide more detailed info. https://drive.google.com/open?id=1ETKndS-2WeboNzhYgh0m6sGB1FdTuNiY

 This is an Excel file that includes the following:

1. Sample Data: This tab includes raw data for one Unit (Unit ID=1260) for week1 and week2 of October 2016 and 2017
2. Summary & Desired Output: This table includes a simple Pivot of the sample data. It also includes a snapshot of Tableau report (desired outcome) followed by KPI definitions and rules. I tried to explain everything in this tab. 

I very much appreciate your help with this and if I can furnish more explanations, please let me know.

Thank You,

Helal