Sales of Previous month if a value is found

[Ibad_Khan]

Hello PBI Community, hope you all're doing good.

I want to cover a scenario for which I've pasted a screen shot below.

Scenario : We want to search and move forward from month Jan to Dec, and if a tag is found in any month, we want to have a sales of that month in all the months after that months. 

For eloboration values needed are highlighted green.

Looking forward for some positive and quick response as usual.

@amitchandak  @v-yadongf-msft  @v-janeyg-msft @v-kkf-msft  @Jihwan_Kim 

Regards,

Ibad Khan.

[v-yueyunzh-msft]

Hi , @Ibad_Khan 

According to your description, you want to "Sales of Previous month if a value is found".

Here are the steps you can refer to :

(1)I do not have your original data , so i create  my test data like this:

(2)We can create a measure :

Measure = var _current_date =MAX('Table'[Date])
var _current_team = MAX('Table'[Team])
var _current_tab = MAX('Table'[Tag])
var _max_date =MAXX( FILTER( ALLSELECTED('Table') , 'Table'[Team]=_current_team && 'Table'[Date]<_current_date && 'Table'[Tag]<> BLANK()) , [Date])
var _pre_sales = SUMX( FILTER(ALLSELECTED('Table'),'Table'[Date] = _max_date && 'Table'[Team]=_current_team) ,[Sales])
return
_pre_sales

(3)Then we can put the fields we need and the measure on the visual and we will meet your need :

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Ibad_Khan]

Hello @v-yueyunzh-msft Thanks alot for your valuable responce , we are very close to it. I'm really sorry that I forgot to mention that we want to look data by team as well as by some other dimensions like zone and regions too.

I'm sharing the dummy data with desired output with you in the form of screenshots, as unfortunately I'm unable to attach the excel sheet as well as Pbix file here.

Sales Data: 

Tag Data:

Desired Output:

Looking forward some postive responce as usual.

Best Regards,

Ibad Khan.

[v-yueyunzh-msft · Accepted solution]

Hi , @Ibad_Khan 

Thank you for your quick reponse, i create the test data as you provided.

Here are the steps you can refer to :
(1)We need to unpivot two tables in Power Query Editor to get the table we need:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc89DoMwDAXgu2RmwM6P02tUnYoYOkQVA3D/rTF9eRJDnDd8dpxlCa/22SVM4X0eze9n+27n4Umyl9qLetCwTn+u4EruaR4tevVF8ggeyT2JoUflzhN4Ik9cxnvk8ok+w2f6PCbPCEZcgAtxGZ/k/v2BwQ3cyI0bCxcq9BW+0nuSB8b76Xj9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Zone = _t, Region = _t, #"22-Jan" = _t, #"22-Feb" = _t, #"22-Mar" = _t, #"22-Apr" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Zone", type text}, {"Region", type text}, {"22-Jan", Int64.Type}, {"22-Feb", Int64.Type}, {"22-Mar", Int64.Type}, {"22-Apr", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Team", "Zone", "Region"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value", "Sales"}})
in
    #"Renamed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc89CsAgDAbQuzi71P9zFKeKg4NIh+r9t1aMaSyFkGR4gS8hMJ/TtTHOjlZzn3suZ6t986k8fVTfIx9agBaoxYvGwaQSqEQqKYWDqRVohVphAOo0OI1O/zoDzqAzy0OEWqAWqf2mJNqBdqjdGiDe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Zone = _t, Region = _t, #"22-Jan" = _t, #"22-Feb" = _t, #"22-Mar" = _t, #"22-Apr" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Zone", type text}, {"Region", type text}, {"22-Jan", type text}, {"22-Feb", type text}, {"22-Mar", type text}, {"22-Apr", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Team", "Zone", "Region"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value", "Tag"}})
in
    #"Renamed Columns"

The table will be transformed like this:

And now we need to apply to the desktop and we do not need to create a relationship between two tables:

(2)We need to create two measures like this:

Tag = var _region = MAX('Sales Data'[Region])
var _zone = MAX('Sales Data'[Zone])
var _team =MAX('Sales Data'[Team])
var _date = MAX('Sales Data'[Attribute])
var _tag = FILTER('Tag Data','Tag Data'[Team]= _team &&'Tag Data'[Zone]=_zone &&'Tag Data'[Region]=_region && 'Tag Data'[Attribute]=_date)
return
MAXX(_tag,[Tag])

Previous = 
var _current_date = MAX('Sales Data'[Attribute])
var _team = MAX('Sales Data'[Team])
var _region =MAX('Sales Data'[Region])
var _zone = MAX('Sales Data'[Zone])
var _previos_tag = MAXX( FILTER('Tag Data' , 'Tag Data'[Team]=_team && 'Tag Data'[Region]=_region && 'Tag Data'[Zone]=_zone && 'Tag Data'[Attribute]<_current_date && 'Tag Data'[Tag]<> BLANK()) , [Attribute])
var _previous_value = FILTER(ALLSELECTED('Sales Data') , 'Sales Data'[Team] =_team && 'Sales Data'[Region]=_region && 'Sales Data'[Zone]=_zone && 'Sales Data'[Attribute]=_previos_tag)
return
SUMX(_previous_value,[Sales])

(3)Then we can put the fields we need on the Matrix measure and we will meet your need.

And for your need we need to configure the Matix parameter in the Format Pane:

In the end we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[amitchandak]

@Ibad_Khan ,

Do you have date? How you created previous month value

example using date table and time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[Ibad_Khan]

@amitchandak  I think this will not work in my scenario. I'm looking for something else.