Showing current and future months

[Anonymous]

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 ideas on how to acheive this would be greatly appreciated.

Cheers

[tex628]

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]

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.

[v-lionel-msft · Accepted solution]

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]

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

Regards

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[tex628]

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]

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]

@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

[Anonymous]

Thanks for the quick response, definately not my area of expertise on that.

So i can definately create the calculated column i gather i then use that as the axis or do i use it as a filter on the visual, that is the part i am not sure on.

Ta

[tex628]

You will need to use it as a filter! If you want you can modify the column slightly to return dates instead of 1's and 0's which would allow you to use it as the axis dimension. 

But right now all you need to do is apply it as a filter 🙂

Br,

J