Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am new to power bi. I have a dataset for the production of one of our plants. Since the plant is temporarily down, we have no production records for a number of recent days. Each batch has a ProdDate field for the day it was produced, PrimeWgt for the quantity produced. This is my main fact table.
I have tried to make visuals using last X months where X is a parameter that can be changed.
PrimeWgt is the measure I want to use in this example.
The following does not work with TODAY(). After troubleshooting, it works if I replace TODAY() by MAX(prodDate) or any date prior to the max date of my data. I want to show last X months of data regardless of slicers (for example product type, customer etc). So if my slicers make the data sparse and many days have no production, I still want data for the last X months starting back from today.
RETURN CALCULATE(sum(V_FCE_STATS_BI[PrimeWgt]), DATESINPERIOD(V_FCE_STATS_BI[ProdDate], TODAY(), -LastXMonths[LastXMonths Value], MONTH))
Solved! Go to Solution.
// Calendar must be connected to ProdDate in the
// fact table and must be marked as the Date table
// in the model.
[PrimeWgt LXM] =
var __lastVisibleDate = MAX( Calendar[Date] )
var __numOfMonthsBack =
SELECTEDVALUE( LastXMonths[LastXMonths Value] )
var __result =
CALCULATE(
SUM(V_FCE_STATS_BI[PrimeWgt]),
DATESINPERIOD(
'Calendar'[Date],
__lastVisibleDate,
-__numOfMonthsBack,
MONTH
)
)
return
IF(
HASONEVALUE( LastXMonths[LastXMonths Value] ),
__result
)
Best
D
// Calendar must be connected to ProdDate in the
// fact table and must be marked as the Date table
// in the model.
[PrimeWgt LXM] =
var __lastVisibleDate = MAX( Calendar[Date] )
var __numOfMonthsBack =
SELECTEDVALUE( LastXMonths[LastXMonths Value] )
var __result =
CALCULATE(
SUM(V_FCE_STATS_BI[PrimeWgt]),
DATESINPERIOD(
'Calendar'[Date],
__lastVisibleDate,
-__numOfMonthsBack,
MONTH
)
)
return
IF(
HASONEVALUE( LastXMonths[LastXMonths Value] ),
__result
)
Best
D
Thank you for making clear the importance of a good date table in a data model!
I have created a date table starting from the beginning of the year of the oldest data to the end of the current year.
I have created fields like Year-Month, Year, month, day, day of week, week number etc. I will play around with it and read on best practices.
Can someone suggest good books to get familiar with power BI? I am not only interested in data models, but in DAX and visuals as well. I want to build a solid foundation of knowledge on powerBI. I have done a decent amount of database administration and data modeling in my past. So far I have listened to Guy in A cube, several documents and articles on Microsoft. There are of course many holes in my knowledge and want to be well rounded in PowerBI. So suggestions for books, sites, documents would be appreciated!
All the best!
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |