cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tejapowerbi123
Helper IV
Helper IV

No Date Table or Date Column and user wants to see last 6 months data.

Hello Community,

I have a unique requirement, In the dataset, I do not have date columns, but the user wants to see only the last six months' data.
In February, a user wants to see data from February to the last six months, February to October.
I do not have a date table in the dataset.
***User wants does not want to use any filter or slicer.

Raw Data Set
Month NameAmount
Jan10
Feb20
Mar30
Apr40
May50
June60
July70
Aug80
Sep90
Oct100
Nov110
Dec120



Final Result
Month NameAmount
Jan10
Feb20
Mar30
Oct100
Nov110
Dec120
1 ACCEPTED SOLUTION
Tahreem24
Super User II
Super User II

@tejapowerbi123 ,

It's little tricky as you want  to Data from March,Feb,Jan,Dec,Nov, and Oct if the latest month is Feb. So as per your need I developed DAX. 

First create Month No. Column as you don't have proper Date column.

Month No. = SWITCH(TableName[Month Name],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"June",6,"July",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
Then create measure like below:
Measure = CALCULATE(SUM(TableName[Amount]),FILTER(A,NOT(TableName[Month No.]>=7-TableName[Month No.]) || TableName[Month No.]>=19-MAX(TableName[Month No.])))
 
So, you'll get below output as per your need:
Capture.PNG
 
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

3 REPLIES 3
Tahreem24
Super User II
Super User II

@tejapowerbi123 ,

It's little tricky as you want  to Data from March,Feb,Jan,Dec,Nov, and Oct if the latest month is Feb. So as per your need I developed DAX. 

First create Month No. Column as you don't have proper Date column.

Month No. = SWITCH(TableName[Month Name],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"June",6,"July",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
Then create measure like below:
Measure = CALCULATE(SUM(TableName[Amount]),FILTER(A,NOT(TableName[Month No.]>=7-TableName[Month No.]) || TableName[Month No.]>=19-MAX(TableName[Month No.])))
 
So, you'll get below output as per your need:
Capture.PNG
 
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

amitchandak
Super User IV
Super User IV

@tejapowerbi123 , do you have a year. because without that how to work on last 6 month

 

With month and year, you can create a date

 

Date = "01-"&[Month] & [year] //change data type as date.

 

Now you can use relative date filter.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you so much so for your suggestion Amit.
Certainly I can add a year column,
 Do you want me to create a new Date table or a new calculated column?

Jan:Date = "01-"&[Month] & [year]

Feb:Date = "02-"&[Month] & [year]

March:Date = "03-"&[Month] & [year]

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors