Forum Discussion

newbie9292's avatar
newbie9292
Helper II
5 years ago
Solved

Help with DAX query

Hi, I am new to power bi and DAX world. I am trying to get data using a DAX query :  Last_year_Kum_AE_Combined = CALCULATE(SUM(AE_Combined[AuftrEing]),FILTER(ALLSELECTED(Date_dim),Date_dim[Date]<=...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi newbie9292

     

    You can create two measures as:

    Running Total = 
CALCULATE (
    SUM('Table'[Sales]),
    FILTER (
        ALLSELECTED('Table'[Date]),
        'Table'[Date] <= MAX ('Table'[Date] ) && YEAR('Table'[Date])=YEAR(MAX('Table'[Date])) 
    )
)
    Ly Running Total = 
CALCULATE (
    SUM('Table'[Sales]),
    FILTER (
        ALLSELECTED('Table'[Date]),
        'Table'[Date] <= MAX ('Table'[Date] ) && YEAR('Table'[Date])=YEAR(MAX('Table'[Date])) && DATEADD('Table'[Date],-1,YEAR)
    )
)

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

FrankAT's avatar
FrankAT
Community Champion
5 years ago

Hi newbie9292 ,

the following example use the year 2017 in the measure. Adjust it to year 2020:

 

 

 

Running Total of 2017 = 
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[CalendarYear] = MAX ( 'Calendar'[CalendarYear] )
            && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[CalendarYear] = 2017
    )
)

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)