Forum Discussion

stephanegodbout's avatar
stephanegodbout
Regular Visitor
6 years ago
Solved

Picking two dates

Hello everyone.   I am new to Power BI with most of my experience coming from the Oracle world -PL SQL etc.  What we are trying to do is the following:   1 - We need to pick the last recordset th...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi stephanegodbout 

    If you want to create reports for comparision, you could create measures using dax as below

     

    max date = CALCULATE(MAX(Sheet2[date]),ALL('Sheet2'))

max date last year =
CALCULATE (
    MAX ( Sheet2[date] ),
    FILTER (
        ALL ( Sheet2 ),
        DATEDIFF (
            Sheet2[Date],
            [max date],
            YEAR
        ) >= 1
            && ( NOT WEEKDAY([date],2)
            IN {
            6,
            7
        } )
    )
)


current value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),Sheet2[date]=[max date]))

last year value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),Sheet2[date]=[max date last year]))

     

    Best Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi stephanegodbout 

If you want to create reports for comparision, you could create measures using dax as below

 

max date = CALCULATE(MAX(Sheet2[date]),ALL('Sheet2'))

max date last year =
CALCULATE (
    MAX ( Sheet2[date] ),
    FILTER (
        ALL ( Sheet2 ),
        DATEDIFF (
            Sheet2[Date],
            [max date],
            YEAR
        ) >= 1
            && ( NOT WEEKDAY([date],2)
            IN {
            6,
            7
        } )
    )
)


current value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),Sheet2[date]=[max date]))

last year value = CALCULATE(SUM(Sheet2[value]),FILTER(ALL(Sheet2),Sheet2[date]=[max date last year]))

 

Best Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.