Change in Month data

[#1]

I have the following formula that is not working.  I am looking in the +/- change Month over Month.  I am only getting the same value as the current month with the below formula.

=Num(Count(Distinct{<[Monthly Issue Severity]={High},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID])-Count(Distinct{<[Monthly Issue Severity]={High},[Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'}>} [Monthly Issue ID]),'0') 

Any assistance would be appreciated!

[#2]

Aiolos, the Load Date is the same field, there are dates that have been imported, and have been set to maintain date status in the load statement.

Load Statement:

Date([Monthly Load Date],'M/D/YYYY') as [Monthly Load Date]

I did try what you mentioned above and there was no change in the +/- calculation, I am still getting the same result as the Monthly Load date calculation. 

[vishsaggi]

Yes, but your excel sheet has not got any dates between 1/1/2017 and 12/1/2016. So i am not sure if this is what you are looking for. Attached is the sample i worked on, if not send me new updated sheet with some dates between those dates.

[stabben23]

Hi Silvia,

this [Monthly Load Date] = {'$(=Max([Monthly Load Date])) will gives you 2017-01-01 if you have correct dateformat.

This [Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'}>} will NOT gives you 2016-12-01 as you expect.

It could gives you a numeric value as Aiolos says, it could gives you 2017-01-00

Put both Expressions in a straigt tablechart without any label, hover over label in chart and see what you get.

[stabben23]

Try this Expression after the '-' in your expression

Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {"$(=max(addmonths([Monthly Load Date],-1))) "}>} [Monthly Issue ID]),

[vishsaggi]

Sorry misunderstood i believe, you were looking for Month on Month right so You want to see the 1/1/2017 counts and your date-1 is like 1 month behind so it will be 12/1/2016. Just follow what Staffan mentioned to use Addmonths and try?

[vishsaggi]

Adding to what Staffan mentioned use below to get the difference. I used this when loading data from excel in the load statement

LOAD Date(Num([Monthly Load Date]), 'MM/DD/YYYY') AS [Monthly Load Date], col2, co3

FROM yourexcelsource;

You H column will be

=count({1}Distinct{<[Monthly Issue Severity] ={'High'},

                     [Monthly Load Date] = {"$(= Max([Monthly Load Date]) )"}>} [Monthly Issue ID])

Your PreviousMonth will be

= Count({1}Distinct{<[Monthly Issue Severity]={'High'},

                      [Monthly Load Date] = {"$(= Max(Date(AddMonths([Monthly Load Date], -1), 'MM/DD/YYYY')) )"}>} [Monthly Issue ID])

Your difference will be +/- MOM

=  Num(Count(Distinct{<[Monthly Issue Severity]={'High'},

                      [Monthly Load Date] = {"$(=Max([Monthly Load Date]))" }>} [Monthly Issue ID])

    -

      Count(Distinct{<[Monthly Issue Severity]={'High'},

                      [Monthly Load Date] = {"$(= Max(Date(AddMonths([Monthly Load Date], -1), 'MM/DD/YYYY')) )"}>}        [Monthly Issue ID]), '0')

[#8]

I still get the same result, using your formula Staffan.

I should be getting a change of -1.  The Monthly Load Date is a date for 12/1 and 1/1, I ensured it in my Load Statement, I am not sure why my formula is not working as it should, by giving me the answer of -1

[#9]

For some reason the formula is not calculating the data for 12/1, I used your advice Staffan and for the period of 12/1, I am getting 0 for Total Monthly Issue ID, when I should get 125

[vishsaggi · Accepted solution]

Did you try my expressions ? It is giving me -1 ?

Attached.

[#11]

You are a life saver!!  Thank you so much!