Obtaining data for different data periods

DAX DAX Calculations
1

Hi,

Problem: After selecting a year in the Year Slicer, I want to obtain a value (ex total of transactions) for the current month of the selected year and December and January values of the previous year.

This is an example of what I intend to achieve if 2017 had been the chosen year

image
image952×404 16.6 KB

Doing this by hard code, it works, but when I try to use it with the SELECTEDVALUE () function to read the chosen year, it fails.
I know it has everything to do with the filter context, but I was not able to find out how to adapt the filter context to obtain only the intended years and months.

Thanks

3

@JoaoMonteiro,

Many different ways to do this, but here’s the path I chose. First step is to create a disconnected table of years using VALUES( Dates[Year]), since you don’t want the slicer to actually filter the visuals – you just want to harvest the value selected.

Sales Current Month Selected Year = 

VAR CurrMo = MONTH( TODAY() )
VAR SelYr = SELECTEDVALUE( 'Disconn Years'[Year] )

VAR Result =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL( Dates ),
            Dates[Year] = SelYr &&
            Dates[MonthOfYear] = CurrMo
    )
)

RETURN
Result

The December of Prior Year calculation follows very similar pattern:

Sales Prev Year Dec = 

VAR SelMo = 12
VAR PrevYr = SELECTEDVALUE( 'Disconn Years'[Year] ) - 1

VAR Result =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL( Dates ),
            Dates[Year] = PrevYr &&
            Dates[MonthOfYear] = SelMo
    )
)

RETURN
Result

January obviously follows the exact same pattern, just changing the SelMo variable from 12 to 1.

image

I hope this is helpful. The solution file attached below.

1 Like
4

@JoaoMonteiro As Brian has suggested here disconnected table is the way to go, and here is another way to do it.

image

Measure = 
VAR CurrentMaxYear =
    CALCULATE ( MAX ( DisDates[Calendar Year Number] ), ALLSELECTED ( DisDates ) )
VAR PrevYear =
    CALCULATE (
        MAX ( DisDates[Calendar Year Number] ),
        DisDates[Calendar Year Number] < CurrentMaxYear,
        REMOVEFILTERS ( DisDates )
    )
VAR CurrentMonth =
    MONTH ( TODAY () ) --Replace with your own criteria of calculating month
VAR PrevYearJanFeb =
    CALCULATETABLE (
        VALUES ( DisDates[Calendar Year Month Number] ),
        DisDates[Month] IN { "January", "February" },
        DisDates[Calendar Year Number] = PrevYear,
        REMOVEFILTERS ( DisDates )
    )
VAR ThisYearCurrentMonth =
    CALCULATETABLE (
        VALUES ( DisDates[Calendar Year Month Number] ),
        DisDates[Month Number] = CurrentMonth,
        DisDates[Calendar Year Number] = CurrentMaxYear,
        REMOVEFILTERS ( DisDates )
    )
VAR OneList =
    UNION ( PrevYearJanFeb, ThisYearCurrentMonth )
VAR Result =
    CALCULATE (
        [Total Sales],
        TREATAS ( OneList, Dates[Calendar Year Month Number] )
    )
RETURN
    Result

image
image1274×708 32.8 KB

1 Like
5

@BrianJ
Thank you so much for your help.

6

@AntrikshSharma Its solution is more complex than BrianJ, but it is always very good to know other ways to solve problems.
Thank you very much for sharing your knowledge.

Regards
Joao Monteiro

1 Like
7

@JoaoMonteiro,

If your request was for transportation, I gave you the equivalent of a 10 speed bike and @AntrikshSharma provided a Ferrari. His takes a bit more effort to work through , but definitely worth the effort since the IN and TREATAS constructs he uses are invaluable tools.

– Brian

1 Like