Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
siva54
Helper I
Helper I

Previous Month Year data's and Selected Month year Data

Hi Team,

 I need a graph or table or any visual for the following criteria,

 

We have Categories and their values  with respective thier Month_years

 We have slicer Month_year , if we select Oct 21 in our slicer then i need to see Sum of previous data(Aug-20, Aug-21, Sep-21) and oct 21 data separately by each category.

 

siva54_2-1636535646555.png

 

 

siva54_1-1636535461390.png

 

Finally what i need is either in table or bar.(if we select oct-21)

 

CategoryPrevious dataSelected year data
Pen272
Fan5414
Chair2918

 

 

if we can shown in bar it should be great.

 

Thanks in advance,

 

 

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @siva54 

According to your description, I can clearly understand your requirement, I think you can try my unusual method to achieve your requirement simply:

First, you still need to get a date column for the comparison in the measure, you can create a calculated column like this and change the data type to get a date column simply:

Date = [Month_Year]

vrobertqmsft_0-1636706753275.png

 

Then you can create a calculated table like this:

Slicer = SUMMARIZE('Table',[Month_Year],[Date])

Then create two measures:

Previous data = SUMX(FILTER('Table',[Date]<MAX('Slicer'[Date])),[Values])

Selected year data = SUMX(FILTER('Table',[Date]=MAX('Slicer'[Date])),[Values])

Then you can create a slicer and a table chart and place them like this:

vrobertqmsft_1-1636706753279.png

 

And you can get what you want.

You can download my test pbix file below

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

View solution in original post

6 REPLIES 6
v-robertq-msft
Community Support
Community Support

Hi, @siva54 

According to your description, I can clearly understand your requirement, I think you can try my unusual method to achieve your requirement simply:

First, you still need to get a date column for the comparison in the measure, you can create a calculated column like this and change the data type to get a date column simply:

Date = [Month_Year]

vrobertqmsft_0-1636706753275.png

 

Then you can create a calculated table like this:

Slicer = SUMMARIZE('Table',[Month_Year],[Date])

Then create two measures:

Previous data = SUMX(FILTER('Table',[Date]<MAX('Slicer'[Date])),[Values])

Selected year data = SUMX(FILTER('Table',[Date]=MAX('Slicer'[Date])),[Values])

Then you can create a slicer and a table chart and place them like this:

vrobertqmsft_1-1636706753279.png

 

And you can get what you want.

You can download my test pbix file below

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

VahidDM
Super User
Super User

Hi @siva54 

 

Understand your Month_year column is a Text Column, so use the following code to find the Previous data :

Previous data =
VAR _SelectedDate =
    SELECTEDVALUE( 'Table'[Month_Year] )
VAR _A =
    CALCULATETABLE(
        ADDCOLUMNS(
            'Table',
            "Date", CONVERT( CONCATENATE( "01-", [Month_Year] ), DATETIME )
        ),
        REMOVEFILTERS( 'Table'[Month_Year] )
    )
VAR _C =
    CONVERT( "1-" & _SelectedDate, DATETIME )
VAR _D =
    FILTER( _A, [Date] < _C )
RETURN
    IF( ISBLANK( SUMX( _D, [Values] ) ), 0, SUMX( _D, [Values] ) )

 

For Selected year data, add value column to the table set aggregation to Sum.

 

Output:

VahidDM_0-1636539657928.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Hi @VahidDM ,

 

Getting below error,

 Yeah, Month_year is Text column

siva54_0-1636541263608.png

siva54_1-1636541332414.png

 

 

Thanks in advance

 

 

Hi @siva54 

 

I think It's because of the blank items in the Month_Year column?

Can you share a PBIX file? [After removing Sensetive data] or dump your data in an excel file and share that with us to have better view on all data.

 


Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Hi @VahidDM ,

 

Measure has been created , but when I'm dragging into a table then getting the below error.

I'm not able to upload the Excel file here.

siva54_0-1636623532583.png

 

Thanks in Advance.

 

Greg_Deckler
Community Champion
Community Champion

@siva54 You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.