Calculate Previous month and next month from the selected month in the slicer

[ghosh_kavitha]

Hi,

Can some one help me to create the measure for Previous month and next month from the selected month in the slicer.

Thanks 

Kg

[v-deddai1-msft]

Hi @ghosh_kavitha ,

The time intelligence function presupposes a complete calendar table. You can follow the link provided by amitchandak to create calendar table. Then use  NEXTMONTH and PREVIOUSMONTH function.

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

Best Regards,

Dedmon Dai

[ghosh_kavitha]

Hi, Actually this is my table. i have to show the dynamic 3 months(previous current next) data in the graph.Thanks Kg

[ghosh_kavitha]

[v-deddai1-msft · Accepted solution]

Hi @ghosh_kavitha ,

Please try to unpoivt your table and group your table by month and add index column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY27EQBBCEJ7Id7gcN371OLYfxsnBgYyIswzAhcWrOaroQxd8upM5Aqw1j2pHM9UrCsC+KRyvCWC2u6KCGfS5j1K+78j8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JanHours = _t, FebHours = _t, MarHours = _t, AprHours = _t, MayHours = _t, JuneHours = _t, JulyHours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JanHours", Int64.Type}, {"FebHours", Int64.Type}, {"MarHours", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Attribute"}, {{"value", each List.Sum([Value]), type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1)
in
    #"Added Index"

Create a measure and use index column to show the values for previous month and next month from the selected month(others are blank).

Would you please inform us more detailed information( your  data(by OneDrive for Business)) if possible? Then we will help you more correctly.

Best Regards,

Dedmon Dai

[amitchandak]

@ghosh_kavitha , You can use time intelligence with calendar table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Tahreem24]

@ghosh_kavitha ,

Use the below DAX:

Prev Month = DATEADD(Table[DateColumn],-1,MONTH)

Prev Month = PARALLELPERIOD(Table[DateColumn],-1,MONTH)

Prev Month = MONTH(MAX(Table[DateColumn]))-1

Likewise, for Next month just replace minus sign with plus sign.