Workaround to use CALCULATE function inside IF statements

[Hyunion]

I want to create a current year quarter sales calculation based on month filter slicer selection but below calculation won't work as Power BI does not let me use CALCULATE function in a TRUE/FALSE expression, is there a way to go about this to make this work?

QTD Sales =

IF (table[Month] = 1,

              CALCULATE(

                           SUM('table'[sales]),

                           'table'[Month] IN { 1 },

                           INT(YEAR(TODAY())) = table[Year]

              )

,IF (table[Month] = 2,

              CALCULATE(

                           SUM('table'[sales]),

                           'table'[Month] IN { 1 }, { 2 },

                           INT(YEAR(TODAY())) = table[Year]

              )

,IF (table[Month] = 3,

              CALCULATE(

                           SUM('table'[sales]),

                           'table'[Month] IN { 1 }, { 2 }, { 3 },

                           INT(YEAR(TODAY())) = table[Year]

              )

,IF (table[Month] = 4,

              CALCULATE(

                           SUM('table'[sales]),

                           'table'[Month] IN { 4 },

                           INT(YEAR(TODAY())) = table[Year]

              )

,IF (table[Month] = 5,

              CALCULATE(

                           SUM('table'[sales]),

                           'table'[Month] IN { 4 }, { 5 },

                           INT(YEAR(TODAY())) = table[Year]

... and so on until month = 12

[dedelman_clng]

Try using variables:

QTD Sales =
  var __ThisYear = INT( YEAR( TODAY() ) )
  var __Sales = CALCULATE(SUM('table'[sales]), table[Year] = __ThisYear )
  return
  IF ( table[Month] = 1,
       CALCULATE( __Sales, table[Month] IN {1} )
 ,IF ( table[Month] = 2,
       CALCULATE( __Sales, table[Month] IN {1}, {2} )
, etc

If that still doesn't work, change the filter expression in CALCULATE to an explicit FILTER:

IF (table[Month] = 1,
    CALCULATE( __Sales, FILTER(table, table[Month] IN {1}) ...etc

PowerBI (DAX) also has a native TOTALQTD() function that should behave the same way if the data is modelled correctly (with a separate Date table)

Hope this helps

David