Previous Month Year data's and Selected Month year Data

[siva54]

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.

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

Category	Previous data	Selected year data
Pen	27	2
Fan	54	14
Chair	29	18

if we can shown in bar it should be great.

Thanks in advance,

[v-robertq-msft · Accepted solution]

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]

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:

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]

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:

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/

[siva54]

Hi @VahidDM ,

Getting below error,

 Yeah, Month_year is Text column

Thanks in advance

[VahidDM]

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/

[siva54]

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.

Thanks in Advance.

[Greg_Deckler]

@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...