Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi - i've created a measure as shown here to create a cumulative sum up to the last sales date, then to give a blank after that date:
However I need to be able to move up / down a date hierarchy and for the measure to work, which is currently is not doing, as shown below if i move up to Month granularity, the meaure no longer returns a blank after the last sale date, and repeats the cumulative total for months beyond the last date:
I think I understand why, becuase in my measure I'm calculating based on the full date key and not the month, however I don't know the solution. Any help would be appreciated.
Thank you,
Joe
Solved! Go to Solution.
@joemillson , This seems fine and should work in a hierarchy. Just make sure that all these Fiscal Year, Qtr, and Month are coming from date table, d_time_Dates and that is marked as date table
Try this too
Cml Actual Invoices =
VAR LastSalesDate = maxx(ALL(f_scoro_SalesInvoicesActual),f_scoro_SalesInvoicesActual[Date Invoice])
RETURN
IF(
max(d_time_Dates[Date]) > LastSalesDate, BLANK(),
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]))))
or
Cml Actual Invoices =
VAR LastSalesDate = maxx(ALL(f_scoro_SalesInvoicesActual),f_scoro_SalesInvoicesActual[Date Invoice])
RETURN
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]) && (d_time_Dates[Date]) <= LastSalesDate))
The first option works just as I needed. Thank you so much!
@joemillson , This seems fine and should work in a hierarchy. Just make sure that all these Fiscal Year, Qtr, and Month are coming from date table, d_time_Dates and that is marked as date table
Try this too
Cml Actual Invoices =
VAR LastSalesDate = maxx(ALL(f_scoro_SalesInvoicesActual),f_scoro_SalesInvoicesActual[Date Invoice])
RETURN
IF(
max(d_time_Dates[Date]) > LastSalesDate, BLANK(),
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]))))
or
Cml Actual Invoices =
VAR LastSalesDate = maxx(ALL(f_scoro_SalesInvoicesActual),f_scoro_SalesInvoicesActual[Date Invoice])
RETURN
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]) && (d_time_Dates[Date]) <= LastSalesDate))
Thanks very much - the first alternative works perfectly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |