Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FatherTheWizard
Resolver I
Resolver I

If ISBLANK cur mth and next mth and NOT ISBLANK prev 2 mths then choose measure and cumulate it

Hi, I have already solved the part to only calculate a sales figure when current month and next month are BLANK and that previous two months are NOT BLANK. When the previous statement is true my DAX writes LTM (last twelve months) with end month being the previous month. Problem is that I would like to cumulate this result to the future but it does not work and probably the reason is that I have used multiple AND statement with PARALLELPERIOD.

 

Below DAX I have used to get the result for one month:

SalesX= IF(AND(AND(ISBLANK(CALCULATE([Invoicing],PARALLELPERIOD('(dim) Date'[date],+1,MONTH))),ISBLANK(CALCULATE([Invoicing]))),AND(NOT(ISBLANK(CALCULATE([Invoicing],PARALLELPERIOD('(dim) Date'[date],-1,MONTH)))),NOT(ISBLANK(CALCULATE([Invoicing],PARALLELPERIOD('(dim) Date'[date],-2,MONTH)))))),CALCULATE([Invoicing LTM (k)],PARALLELPERIOD('(dim) Date'[date],-1,MONTH))/12,0)

 

Below the basic cumulating DAX which does not work here (but works if I just use it for basic Sales measure I have.

SalesX cumul. = CALCULATE([SalesX],FILTER('(dim) Date','(dim) Date'[year_month]<=MAX('(dim) Date'[year_month])))

 

PS: So if I do the cumulating DAX for example for "invoicing" measure, it works. Below as appendix my LTM invoicing dax if it matter: CALCULATE([Invoicing],DATESINPERIOD('(dim) Date'[date],MIN('(dim) Date'[date]),-1,YEAR))

3 REPLIES 3
Greg_Deckler
Super User
Super User

@FatherTheWizard - Hard to follow what might be going on without sample data. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  Appreciate tips for the problem and how to navigate in the forum.

 

I decided to leave sample set out since the dataset behind is just basic "customer + invoicing + date" -table and you cannot find anything realle useful or unusual from that. (Also my opening post was already quite busy.)

 

Point is that using the basic "cumulating DAX" (Filter, ALL, MAX combo) do not work when I use with my aforementioned "SalesX" DAX inside of the "cumulating DAX". If I use with basic "Sum of invoicing" measure  inside the cumulating DAX, it works normally on my data model. I do not find any help to this by Googling since it is quite unique Time intelligence need - or cumulating need. 

 

I wish the cumulating would be easier than it is. I can "see" the correct figure on correct month in the matrix table for my SalesX measure but when I cumulate it using the "cumulating DAX" it does not give anything else but zeroes/blanks - also on the month which has a value for Sales X measure. Probably my AND+PARALLELPERIOD combo is doing something for the "cumulating DAX" that I do not understand in the context of how PBI Time Intelligence works.



Maybe I will try to find the solution by myself since I am not sure how to present it better here.

@FatherTheWizard , Try like

 

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))
Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-2,MONTH)) 

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-2,MONTH))  

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],EOMONTH(Sales[Sales Date]),0),-2,MONTH)) 
Rolling 2 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],EOMONTH(Sales[Sales Date]),-1),-2,MONTH)) 

 

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors