Forum Discussion

Brinki's avatar
Brinki
Frequent Visitor
2 years ago

Total is calculated group is incorrect

Trying to create a calculation group for PY to current week, however total is incorrect, any help how could I tackle such issue.

 

This is the measure I'm using in Tabular Editor

SUMX(VALUES(DimDate[Year]),
IF(
   [CY Test] && [ShowValueForDates] = TRUE(),
   CALCULATE(
       SELECTEDMEASURE (),
          FILTER(
                'DimDate',
                'DimDate'[Year] = YEAR(TODAY()) - 1
            ),
            VALUES ('DimDate'[WeekNum])),BLANK()))

 

Need Help

3 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Brinki,

     

    Why are you saying it's wrong and what are you expecting?

    • Brinki's avatar
      Brinki
      Frequent Visitor
      2 years ago

      Would expect to have total till current week this year, not the full year total

      Should be around 14M

      • Wilson_'s avatar
        Wilson_
        Icon for Memorable Member rankMemorable Member
        2 years ago

        Brinki,

         

        The problem with your existing measure is your conditions only determine whether or not to show a value. Your check for [ShowValueForDates] doesn't actually filter the calculation. At the total level, all the weeks are available, which returns all 52 weeks when you call "VALUES ('DimDate'[WeekNum])" as a filter function. That doesn't filter anything out either.

         

        Try something like:

         

        VAR MaxWeekNum = MAX ( 'DimDate'[WeekNum] )
VAR LastYear = YEAR ( TODAY() ) - 1
VAR DisplayConditions = [CY Test] && [ShowValueForDates]
RETURN
IF (
    DisplayConditions,
    CALCULATE (
        SELECTEDMEASURE(),
        REMOVEFILTERS ( 'DimDate' ),
        LastYear,
        'DimDate'[WeekNum] <= MaxWeekNum
    )
)

         

         

        If that doesn't work, can you share a small mock file?


        ----------------------------------
        If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

         

        P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.