Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Showing current and future months

Hi All Probably an easy thing to do but i am trying to have on a line chart the current month as the AXIS plus any month moving forward we do not want to see the previous months and their data. Any...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Has your problem been solved? If not, please show us a sample data.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

tex628's avatar
tex628
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous,

You could do it by creating a calculated column to determine if each date matches the condition you are looking for. 

Check column = 
IF( Calendar[Date]>=TODAY();1;
IF( Calendar[Year]=YEAR(TODAY()) && Calendar[Month]=MONTH(TODAY()) ;1;
0))


Br,
J

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi again

no joy on what i was trying to acheive 😞

 

So some more detail and a change of direction is needed i think, in the dataset which is one big table each month is allocated a number (PERIOD) 01,02,03 etc but they are not as you expect July is 01 Aug 02 Sep 03 etc. The table also has of course a month as well Jan, Feb, Mar etc but has NO transaction dates

 

I need to put a filter on the visual that will say ok you are now in the month of April =10 and you want to show 6 months in the future so that would be May =11 June =12 July =1 Aug=2 Sep=3 Oct=4

 

So BI needs to look at the current month then using smarts display the next 6 months i know that manually i can drop the PERIOD as a fiter then manually select the next six months and the visual then changes but the goal is to make it happen automaticaly

 

Sorry a bit tired so this may not make sense.

 

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Alright,

    This was way more complicated than i thought. You will need a Financial Year column together with your period column. I have a table reference below the dax:

     

    Calculated column = 
VAR Cm = MONTH(today())
VAR Cy = YEAR(today())
VAR Fp = IF(Cm > 6 ; Cm -6 ; Cm +6)
VAR Fy = IF(Cm > 6 ; Cy ; Cy -1)
VAR Ep = IF(Fp > 6 ; Fp -6 ; Fp +6)
VAR Ey = IF(Fp > 6 ; Fy +1 ; Fy)
Return
IF( 
OR(
AND([PERIOD] >= Fp ; [Financial Year] = Fy) ;
AND([PERIOD] <= Ep ; [Financial Year] = Ey)
) ; 
1 ;
0 )

     

     

     

    Year Month Financial period Financial Year
    2020 1 7 2019
    2020 2 8 2019
    2020 3 9 2019
    2020 4 10 2019
    2020 5 11 2019
    2020 6 12 2019
    2020 7 1 2020
    2020 8 2 2020
    2020 9 3 2020
    2020 10 4 2020
    2020 11 5 2020
    2020 12 6 2020
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi

       

      That is very close but missed the periods 10,11,12 which are April, May, Jun

      I tracked the problem to the If statement

      Current period 4

      FP =10

      FY = 2019

      EP = 4

      EY = 2020

      First clause of the if statement would be incorrect as it would default to 2019

      I am trying to now resolve the IF statement component

       

      The data set has 2 years of data in it 2019, 2020

       

      Regards

      Skinni

       

      • tex628's avatar
        tex628
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous , 

        Is this not the desired interval? 



        If not, could you make an excel table in the same way and show me what you're after?

        Br,
        J

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Has your problem been solved? If not, please show us a sample data.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks i found another way to handle the problem, so will be closing this question.

      Regards