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.

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