Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
hello
need help with mtd
factTable[move_usd]
dimDate[DateID]
move_total:=CALCULATE(sum(factTable[move_usd]),DATESMTD(dimDate[DateID]))
it works, but my calendar is greater than today and fact data is before today
so it calculates unit EOF calendar
i added:
move_total_2:=IF(lastdate(dimDate[DateID]) >= Today(), Blank()
,CALCULATE(sum(factTable[move_usd]),DATESMTD('dimDate'[DateID])))
but if month is not closed on month/year level it shows empty value
how to:
1. calculate MTD
2. until today
3. with last child(not greater than yeasterday) on month/year level
before i used MDX,'mosha formula' , now i'm re-writing on tabular
in mdx i used MTD + scope
how i can handle it in dax?
thank you.
Have you considered using DATESMTD ?
oh, sorry i made a mistake in my request
yes of course i'm using DATESMTD (in original request i wrote YTD)
but the question is about next
on screenshot two measures:
MTD_v2:=
CALCULATE(
sum(fact_Table[move_usd]),
DATESMTD('dim_Date'[DateID]))
MTD_v1:=
IF(
ISINSCOPE(dim_Time[DateID]),
if(
MAX(dim_Time[DateID]) >= Today(),
Blank(),
MTD_v2
)
, inv_mtd)
so the issue is
1.
for mtd_v1 - on february 2023 level and on 1Q and 2023 - empty, but 26th feb(lastFactDate) is needed
so i need value from last fact date on month,q, year level
2.
fir mtd_v2 - values should be empty for days after 26th feb
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
42 | |
21 | |
20 | |
13 |
User | Count |
---|---|
125 | |
41 | |
38 | |
26 | |
24 |