cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PKGARG Helper I
Helper I

Current and previous year comparison

I have Data Like,

Fin yearBill MonthQty
2015-2016April223
2015-2016May308
2015-2016June602
2015-2016July726
2015-2016August527
2015-2016September536
2015-2016October289
2015-2016November406
2015-2016December672
2015-2016January575
2015-2016February784
2015-2016March213
2016-2017April233
2016-2017May193
2016-2017June562
2016-2017July517
2016-2017August531
2016-2017September353
2016-2017October227
2016-2017November249
2016-2017December629
2016-2017January599
2016-2017February600
2016-2017March243

 

Now what i write in measure column to calculate monthwise comparison.

5 REPLIES 5
spuder Resolver IV
Resolver IV

Re: Current and previous year comparison

Hi,

 

I'm not really sure if I am right.

 

But for me it looks like a normal job for a pivot table (or matrix in Power BI)

 

example.jpg

scottsen Memorable Member
Memorable Member

Re: Current and previous year comparison

You will want to create a date/calendar table, then create a relationship between your data... and the new calendar table.  Then you can use function like "SAMEPERIODLASTYEAR".

Microsoft
Microsoft

Re: Current and previous year comparison


@PKGARG wrote:

I have Data Like,

Fin year Bill Month Qty
2015-2016 April 223
2015-2016 May 308
2015-2016 June 602
2015-2016 July 726
2015-2016 August 527
2015-2016 September 536
2015-2016 October 289
2015-2016 November 406
2015-2016 December 672
2015-2016 January 575
2015-2016 February 784
2015-2016 March 213
2016-2017 April 233
2016-2017 May 193
2016-2017 June 562
2016-2017 July 517
2016-2017 August 531
2016-2017 September 353
2016-2017 October 227
2016-2017 November 249
2016-2017 December 629
2016-2017 January 599
2016-2017 February 600
2016-2017 March 243

 

Now what i write in measure column to calculate monthwise comparison.


@PKGARG

You'll have to re-model your data as below and create a calendar table as @scottsen suggests.

 

calendar = 
ADDCOLUMNS (
    CALENDAR ( "2015-04-01", "2018-03-30" ),
    "Fin Year", IF (
        MONTH ( [Date] ) < 4,
        CONCATENATE ( CONCATENATE ( YEAR ( [Date] ) - 1, "-" ), YEAR ( [Date] ) ),
        CONCATENATE ( CONCATENATE ( YEAR ( [Date] ), "-" ), YEAR ( [Date] ) + 1 )
    ),
    "YearMonth", CONCATENATE (
        CONCATENATE ( YEAR ( [Date] ), "-" ),
        RIGHT ( CONCATENATE ( "0", MONTH ( [Date] ) ), 2 )
    )
)

Capture.PNG

 

Then create relationship between those two tables.

Capture.PNG

 

Then create two measures like

 

Previous Month Qty = CALCULATE(SUM(yourTable[Qty]), PREVIOUSMONTH('calendar'[Date]))

Previous Year Qty = CALCULATE(SUM(yourTable[Qty]), SAMEPERIODLASTYEAR('calendar'[Date]))

Capture.PNG

 

Check more detail in the attached pbix.

 

 

PKGARG Helper I
Helper I

Re: Current and previous year comparison

I Am Trying to Convert My Data    Fin Year & Month  in Date. How can i add column for Date by Using...

 

Fin.Year           Month    Date

2015-2016       April       ?

2016-2017       April       ?

2015-2016       May        ?

2016-2017       May       ?

scottsen Memorable Member
Memorable Member

Re: Current and previous year comparison

I would probably do some string munging and use DATEVALUE.  Something like:

 

=DATEVALUE([Month] & " 1," & LEFT([Fin.Year], 4))

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors