Set Analysis Sum Across Dimension

[erikzions]

I have a table with a date dimension.  I need to sum the previous quarters balance.  I have a field for the previous quarter.  I am having a hard time getting my set analysis to sum a previous date in the current dates dimension.  I have tried many things.  Below are two attempts.  I must be having a brain cramp because I know I have done this before.  Any help?

sum({< [As of Date] = {PreviousQuarter} >} [Balance])

also tried

sum({< [As of Date] = P({< [As of Date] = {'=PreviousQuarter'} >}) >} [Balance])

Date	Previous Quarter	Balance	Previous Quarter Balance
Sep 17	N/A	50	N/A
Oct 17	Sep 17	75	75
Nov 17	Sep 17	100	75
Dec 17	Sep 17	125	75
Jan 18	Dec 17	150	125
Feb 18	Dec 17	175	125

[swuehl]

Do you have created an AsOf Table that really links to the previous quarter? How does your model look like?

Or maybe just use

=Above(Sum(Balance))

[erikzions]

No link to the previous quarter.  Just a field in the calendar table of previous quarter.

=Above would work except I have more than just quarters, and the user can select non sequential dates.

[swuehl]

An AsOf table may solve your problem:

The As-Of Table

[erikzions]

I have been unable to get this to work.  I have included a simple example of my needs.  My goal to get a quarter to date percent. Any help would be appreciated. 

[sunny_talwar]

One option is to do like this

Data:

LOAD * INLINE [

    Date, Balance

    09/30/2017, 50

    10/31/2017, 75

    11/30/2017, 100

    12/31/2017, 125

    01/31/2018, 150

    02/28/2018, 175

];

Dates:

LOAD * INLINE  [

Date, Previous Quarter

    09/30/2017, 06/30/2017

    10/31/2017, 09/30/2017

    11/30/2017, 09/30/2017

    12/31/2017, 09/30/2017

    01/31/2018, 12/31/2017

    02/28/2018, 12/31/2017

];

Left Join (Dates)

LOAD Date as [Previous Quarter],

Balance as [Previous Quarter Balance]

Resident Data;

[sunny_talwar · Accepted solution]

This might be the asoftable approach

Data:

LOAD * INLINE [

    Date, Balance

    09/30/2017, 50

    10/31/2017, 75

    11/30/2017, 100

    12/31/2017, 125

    01/31/2018, 150

    02/28/2018, 175

];

AsOfTable:

LOAD Date as AsOfDate,

Date,

'CQ' as Flag

Resident Data;

Concatenate (AsOfTable)

LOAD Date as AsOfDate,

Date(QuarterStart(Date)-1) as Date,

'PQ' as Flag

Resident Data;

[erikzions]

This approach will work.  I was hoping for a front end solution so I would not have to change my data model.  This solution requires extensive changes to my dashboards to accommodate a current quarter set analysis in each balance total.  It also requires all users to know set analysis to correct.  If there is no other way though, I will have to implement it like this.  Thank you for your help.  I will mark this correct in a little while to see if there is a more correct answer out there.

[Digvijay_Singh]

Not sure but u may see if this expression can help in some way, PQB is expression label -

if([Previous Quarter]=Above(Total Date),Above(Total Balance),if([Previous Quarter]=Above(Total [Previous Quarter]),Above(Total PQB)))

[swuehl]

The AsOf table is an additional table that links to your existing Date / Quarter dimension.

You only need the AsOf dimension and set analysis where you need to compare current / previous quarters in the same table.

For every other chart that is just using the current quarter data, just use the already existing dimension, no need to use the type field (or Flag as it is called in Sunny's example) in a set expression.