IF formula with dates and sum with months

[dsabsi]

Hi Guys, 

I'm fairly new to Power BI and my DAXX knowledge is very low, so please bear with me. 

I'm trying to combine an IF formula with dates and with months, and i've searched the PB community but couldn't find the answer anywhere.

Month column: DIM_DATE[MONTHNAME]

I want to create a filter in which I can create a sum IF formula. 

If it's 12 november, include the months januari till october from 2017

If it's 13 december, include the months januari till november from 2017

if it's 7 january 2018, include all the months from januari till december 2018. 

I started like: if(today()=DATE(2017;11;8);count(DIM_DATE[MONTHNAME]) this but I can't find the formula which states count(januari till october from 2017)

With this formula, I can create a filter which filters my financial records so that it shows the SUM after a certain period.

Can anyone please help a brother out? 

Cheers, 

DSabsi

[Greg_Deckler]

Do the specific days matter or is it just based upon what month today happens to be in? Also, what are we counting here? Just the number of days?

[dsabsi]

Hi @MSMPekowski,

The specific days matter, because I want to check what the total outstanding amount is after a certain period. 

This had to do with the fact that after these periods, certain periods are declared. 

So if its 8 november, sum of january till october from 2017. 

If it's 3 january 2018, sum januari till december from 2017

If it's 8 february 2018, sum januari 2017 till january 2018

if it's 8 march 2018, sum january 2017 till february 2018

if it's 5 april 2018, sum january 2017 till march 2018

if it's 10 may 2018, sum january 2017 till april 2018. 

The above dates are actually what I want to create in this formula. 

I really appreciate your swift response and I hope you can provide me with the answer to this @Greg_Deckler

Cheers, 

Dsabsi

[Greg_Deckler · Accepted solution]

Perhaps try something like this:

Measure = SWITCH(TODAY(),
                DATE(2017,11,8),SUMX(FILTER('Calendar',MONTH([Date])>=1 && MONTH([Date]<=10)),[Column]),
                DATE(2018,1,3),SUMX(FILTER('Calendar',MONTH([Date])>=1 && MONTH([Date]<=12)),[Column]),
                BLANK())

[Zubair_Muhammad]

Hi @dsabsi

Try this DAX measure. Modify Table and Field Names according to your case

MySum =
CALCULATE (
  SUM ( Table1[Amount] ),
  FILTER (
    ALL ( Table1 ),
    Table1[Dates]>= 01 / 01/ 2017
    && Table1[Dates]
   <= DATE ( YEAR ( VALUES ( Table1[Dates] ) ), MONTH ( VALUES ( Table1[Dates] ) ) - 1, 31 )
    )
)

See the attached file Download file