cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
philpringuer Frequent Visitor
Frequent Visitor

DAX or M language, what am I doing?

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?
 
Fires_CountVsCumulativeCount.JPGSame results - but different measures for VALUES...
 Any recommendations would be appreciated.
 
Thanks for your time.
 
phil
4 REPLIES 4
Community Support Team
Community Support Team

Re: DAX or M language, what am I doing?

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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
philpringuer Frequent Visitor
Frequent Visitor

Re: DAX or M language, what am I doing?

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

Cumulative Count - Compare Two FYs.JPGX-axis is presented as mm-dd

Community Support Team
Community Support Team

Re: DAX or M language, what am I doing?

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])))
06.PNG

Best regards,

Dina Ye

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Super User I
Super User I

Re: DAX or M language, what am I doing?

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. 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors