Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
What I am doing is diplaying the current months $/Runtime and the $/Runtime for the last 3 (active) months. What is happening is the totals are including the zero data months if they exist. I have three tables Runtime, Costs, and Dates (date table) as seen below(excel example). To the right of the pivot table in the Prev3 column is how i am trying to get power bi to output the data. The yellow highligted areas are how power bi is outputting the data.
The DAX I have is right here. Additionally I need this to ignore date filters that are applied with a slicer if possible.
Measure =
var LD = EOMONTH(ENDOFMONTH(LASTDATE(Runtime[Date (bins)])),-1)
var FD = DATEADD(LASTDATE(STARTOFMONTH(PREVIOUSMONTH(Runtime[Date (bins)]))),-2,MONTH)
return
CALCULATE(DIVIDE(SUM(Costs[Maint Cost]),SUM(Runtime[RuntimeTime])),
FILTER(ALL(Dates),
Dates[Date]<=LD&&Dates[Date]>=FD
)
)
Here is the visual i am using this meaure for:
How data is organized:
Data Pivot, Sample Calcs:
Solved! Go to Solution.
Hi @jignaski18 ,
Please check:
Create measures like so:
Measure = IF(SUM(Costs[maint cost])=BLANK(),1,0)
maxx date =
VAR m_ =
MAX ( Dates[YearMonth] )
VAR yearmonth =
MAXX (
TOPN (
3,
FILTER (
CALCULATETABLE ( VALUES ( Dates[YearMonth] ), ALLSELECTED ( Dates[YearMonth] ) ),
[YearMonth] < m_
&& [Measure] <> 1
),
[YearMonth], DESC
),
[YearMonth]
)
RETURN
CALCULATE ( LASTDATE ( Dates[Date] ), Dates[YearMonth] = yearmonth )
minx date =
VAR m_ =
MAX ( Dates[YearMonth] )
VAR yearmonth =
MINX(
TOPN (
3,
FILTER (
CALCULATETABLE ( VALUES ( Dates[YearMonth] ), ALLSELECTED ( Dates[YearMonth] ) ),
[YearMonth] < m_
&& [Measure] <> 1
),
[YearMonth], DESC
),
[YearMonth]
)
RETURN
CALCULATE ( FIRSTDATE( Dates[Date] ), Dates[YearMonth] = yearmonth )
$/Runtime =
VAR MIN_ = [minx date]
VAR MAX_ = [maxx date]
VAR CurrentDate =
FIRSTDATE ( Dates[Date] )
VAR mindate =
CALCULATE ( FIRSTDATE ( Dates[Date] ), ALL ( Dates ) )
RETURN
IF (
DATEDIFF ( mindate, CurrentDate, MONTH ) >= 3,
CALCULATE (
DIVIDE ( SUM ( Costs[maint cost] ), SUM ( Runtime[runtime] ) ),
Dates[Date] >= MIN_
&& Dates[Date] <= MAX_
)
)
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jignaski18 ,
Please check:
Create measures like so:
Measure = IF(SUM(Costs[maint cost])=BLANK(),1,0)
maxx date =
VAR m_ =
MAX ( Dates[YearMonth] )
VAR yearmonth =
MAXX (
TOPN (
3,
FILTER (
CALCULATETABLE ( VALUES ( Dates[YearMonth] ), ALLSELECTED ( Dates[YearMonth] ) ),
[YearMonth] < m_
&& [Measure] <> 1
),
[YearMonth], DESC
),
[YearMonth]
)
RETURN
CALCULATE ( LASTDATE ( Dates[Date] ), Dates[YearMonth] = yearmonth )
minx date =
VAR m_ =
MAX ( Dates[YearMonth] )
VAR yearmonth =
MINX(
TOPN (
3,
FILTER (
CALCULATETABLE ( VALUES ( Dates[YearMonth] ), ALLSELECTED ( Dates[YearMonth] ) ),
[YearMonth] < m_
&& [Measure] <> 1
),
[YearMonth], DESC
),
[YearMonth]
)
RETURN
CALCULATE ( FIRSTDATE( Dates[Date] ), Dates[YearMonth] = yearmonth )
$/Runtime =
VAR MIN_ = [minx date]
VAR MAX_ = [maxx date]
VAR CurrentDate =
FIRSTDATE ( Dates[Date] )
VAR mindate =
CALCULATE ( FIRSTDATE ( Dates[Date] ), ALL ( Dates ) )
RETURN
IF (
DATEDIFF ( mindate, CurrentDate, MONTH ) >= 3,
CALCULATE (
DIVIDE ( SUM ( Costs[maint cost] ), SUM ( Runtime[runtime] ) ),
Dates[Date] >= MIN_
&& Dates[Date] <= MAX_
)
)
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This does work in table format when you have the YearMonth in the rows. When I try to use it in a bar chart it wouldnt return the restult. I did get it figured out though. The fix was in the curent date variable in the $/runtime formula
$/Runtime =
VAR MIN_ = [minx date]
VAR MAX_ = [maxx date]
VAR CurrentDate =
CALCULATE(
FIRSTDATE(
Dates[Date]),
FILTER(all(Dates),Dates[month index]=0)
VAR mindate =
CALCULATE ( FIRSTDATE ( Dates[Date] ), ALL ( Dates ) )
RETURN
IF (
DATEDIFF ( mindate, CurrentDate, MONTH ) >= 3,
CALCULATE (
DIVIDE ( SUM ( Costs[maint cost] ), SUM ( Runtime[runtime] ) ),
Dates[Date] >= MIN_
&& Dates[Date] <= MAX_
)
)
Previous visual (incorrect forumulas):
Your Formulas (the numbers are right in table):
@jignaski18 , for this month and last month you can use 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 Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
and rolling like
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,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.
Your solution does work, ONLY if there is continuous data. I am trying to address the calculation to work as below.
Your DAX results in the values highlighted in yellow. I need the results in green(right most columns). Technically the formula isnt "Previous three months". I would be more along the lines of "Previous three months that contain data"
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |