DAX or M language, what am I doing?

[philpringuer]

Hi Team,

I have years of fires data in a table named qryAllFiresSince1972Lite.
Each fire has a unique [EventID] number.

Fires need to be summarised by Financial Year - i.e. starts in JUL, ends in JUN.

I can get monthly counts of fires per Financial Year.

I cannot get a cumulative count of fires by month (e.g. JUL = JUL; AUG = JUN + JUL; SEP = JUN + JUL + AUG), despite feeling like I've used the right 'pattern'.  Obviously it's ME that's doing something wrong, but I can't work out what that is.

In the attached image, the top matrix is of fire counts by month, which works as expected.

* ROW headings as [FireStarted_MMM] ~ a calculated column as 

FireStarted_MMM = left([FireStarted-YYMMDD].[Month],3)

where

FireStarted_YYYYMM = (year([FireStarted-YYMMDD])*100) + month([FireStarted-YYMMDD])

This to return months like JUN, JUL etc.

* COL headings as [FireStartedFY] ~ a calculated column as 

FireStartedFY = if(

[FireStarted_Month] > 6,

[FireStarted_Date].[Year] & "/" & [FireStarted_Date].[Year] + 1,

[FireStarted_Date].[Year] - 1 & "/" & [FireStarted_Date].[Year]

)

This to return in "2017/18" format.

* VALUES as Number_Fires ~ a measure defined as

Number_Fires = DISTINCTCOUNT(qryAllFiresSince1972Lite[EventID])

The bottom matrix is my attempt at a cumulative total - same set up, but with 

* VALUES as CumulativeCountOfFiresPerFY ~ a measure defined as

CumulativeCountOfFiresPerFY = CALCULATE (

COUNTROWS(qryAllFiresSince1972Lite),

FILTER (

qryAllFiresSince1972Lite,

qryAllFiresSince1972Lite[FireStarted_Date] <= MAX(qryAllFiresSince1972Lite[FireStarted_Date])

)

)

Both matrices generate the same result - I was hoping the 2nd would produce an "up to now" cumulative count.

Any ideas where I'm failing?

Possibly (?) the way I'm handling dates as ROW values?

Same results - but different measures for VALUES...

 Any recommendations would be appreciated.

Thanks for your time.

phil

[v-diye-msft]

Hi @philpringuer 

I’m confused about your request : cumulative count of fires by month:

JUL = JUL; AUG = JUN + JUL; SEP = JUN + JUL + AUG (Financial Year - i.e. starts in JUL, ends in JUN.)

Could you please specify which year the June belongs to in your logic? if June belongs to the last fiscal year, it’s not reasonable to calculate it into this year. If it belongs to this year, then it points to the future month which probably doesn’t come currently. It’s not reasonable either.

Please confirm whether you’d like to get below results  :

JUL = JUL; AUG = JUL + AUG;  SEP = JUL + AUG + SEP…..JUN= JUL+AUG+SEP+….+MAY

Also, it’s preferred that share us your pbix file (upload to Onedrive and share your link here) which masking the confidential info. It’s easier for us to provide the further support .

Best regards,

Dina Ye

[philpringuer]

Hi Dina,

Sorry for the confusion, Dina - my mistake.

I can confirm that I meant I'm hoping to achieve cumulative counts by month of Financial Year, where:
JUL = JUL

AUG = JUL + AUG
SEP = JUL + AUG + SEP

JUN = JUL + AUG + SEP + OCT + NOV + DEC + JAN + FEB + MAR + APR + MAY + JUN

July is the first month of the FY; so I'm hoping to start with July, and end with June.

The following chart (mocked up in Excel) is what I'm trying - unsuccessfully - to replicate in PBI...

I can't make your life easier by uploading a file to OneDrive - I don't have a personal OneDrive account, and the business one I have access to would be restricted.  If that makes it too hard, then I'll understand you rescinding your offer of help!

phil

X-axis is presented as mm-dd

[v-diye-msft]

Hi @philpringuer ,

Not sure if you'd like to show like this:

I added a new calculated column to replace the month with fiscal month: and then use the cumulative measures:

FiscalMonth = IF([FactMonth]>=7,[FactMonth]-6,[FactMonth]+6)

2016-2017Cumulative = CALCULATE(SUM('Table'[2016/2017]),FILTER(ALL('Table'),[FiscalMonth]<=MAX([FiscalMonth])))

2017-2018Cumulative = CALCULATE(SUM('Table'[2017/2018]),FILTER(ALL('Table'),[FiscalMonth]<=MAX([FiscalMonth])))

Best regards,

Dina Ye

[Anonymous]

You are also going to want to use a dedicated data table. You can use the columns from that table in your measures. You really do not want ever use FILTER over a fact table.