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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Matej
Helper I
Helper I

Time Intelligence with cumulative month

Hi experts, 

I'm stuck with this time intelligence problem: 
I have a measure that calculates cumulative sales in the current month. 

TESTtimeIntelMonth =
VAR CurrentDay = TODAY()
VAR MaxDate = MAX('Transaction Date'[*Date (trans)])
VAR CurrentMonth = MONTH(CurrentDay)
VAR CurrentYear = YEAR(CurrentDay)
VAR FirstDay = DATE(CurrentYear, CurrentMonth, 1)
VAR SO = IF(TODAY() = DATESBETWEEN('Transaction Date'[*Date (trans)], FirstDay, CurrentDay), [TESTSalesOrder], 0)
VAR Res = SUMX(
FILTER(
ALL('Transaction Date'),
'Transaction Date'[*Date (trans)] >= DATE(CurrentYear, CurrentMonth, 1)
&& 'Transaction Date'[*Date (trans)] <= MaxDate
&& 'Transaction Date'[*Date (trans)] <= CurrentDay
),
[TESTSalesOrder]
)
RETURN
 IF(FORMAT(MaxDate, "YYYYMM" ) = FORMAT(CurrentDay, "YYYYMM"), Res, BLANK())

This measure retuens the following table:
Matej_0-1688461172723.png

and graph 

Matej_1-1688461200676.png

 

 --- This is what I would like to achieve (currently it is doable with slicer being attached to the visual. I want to make the report "no-maintenance needed" one so would like to get rid of the slicer):
Matej_2-1688461320223.png

 

I was thinking about achieving this by making every day before today a zero and then filtering visual to not show any zeros. My problem is that as much as I am trying I can't figure out how to tell DAX to still sum cumulative days in order but make every day before today a zero.

Any advice/recommendation would behighly appreciated. Thanks, 



0 REPLIES 0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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