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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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