topic Re: calculate by MTD just giving blank output in DAX Commands and Tips

calculate by MTD just giving blank output

plawrence14 — Wed, 31 May 2023 12:36:07 GMT

Hi,

I am very new to Power BI so please forgive me for any ignorance. I did search for a solution but the solutions I found did not seem to work for me.

I have an Invoice table with lots of different columns on. One being 'invoice value' and one being 'dateCreated'.

I created my own Date table with Dates and offsets. Within is a column called 'dates' with every date from 2015 to 2030.

I linked the two tables by the 'dateCreated' and 'date' columns. I have tried this one-to-many, many-to-one, and many-to-many.

If i do

invValue = CALCULATE(sum(Invoice[InvoiceValue]))

I get the value of all invoice for all time so I know that works.

Now I would like to get last month's so I have tried writing different measures. The first:

invoiceValueMTD = SUMX(FILTER(Dates, Dates[MonthOffset]=-1),Invoice[Total Invoice Value])

and the second:

invoiceValueMTD = CALCULATE(SUM(Invoice[InvoiceValue]),DATESMTD(Dates[Date]))

Both give blank outputs on the card

Could anyone help please?

Thanks

Paul

Re: calculate by MTD just giving blank output

Greg_Deckler — Wed, 31 May 2023 12:53:27 GMT

plawrence14 See if the techniques in this video help:

Also: To **bleep** With Time Intelligence - Microsoft Fabric Community

Re: calculate by MTD just giving blank output

plawrence14 — Wed, 31 May 2023 15:50:34 GMT

HI Greg,

I think thats pointing me in the right direction, I shall make a new dates table and see where that goes

Thanks you I will update stommorow after i have played some more

Paul

Re: calculate by MTD just giving blank output

plawrence14 — Thu, 01 Jun 2023 08:48:44 GMT

HI,

So I have created new date tables several time and had a couple of hours with chat GPT several different forulas keep giving teh same blank out put on the card visuals.

Any ideas would be appreciated

Thansk

Paul

Re: calculate by MTD just giving blank output

plawrence14 — Thu, 01 Jun 2023 12:17:24 GMT

Hi Everyone,

So i have got this working, But the way i did it feels wrong still result is result

So i then added the following measures to the invoice table

CurrentDate = NOW()
CurrentDay = Day(NOW())
CurrentMonth = MONTH(NOW())
CurrentYear = YEAR(NOW())

then i added these colums to the invoice table

Month = MONTH(invoice[DateCreated])
MonthName = FORMAT(invoice[DateCreated],"mmm")
Quarter = QUARTER(invoice[DateCreated])
Year = YEAR(invoice[DateCreated])

DayOffset = CONVERT(invoice[DateCreated]-TODAY(),INTEGER)
Week Offset = ROUNDUP((invoice[DateCreated]-TODAY ())/7,0)
MonthOffset = (invoice[YearOffset]*12) + (invoice[Month] - Invoice[CurrentMonth])
YearOffset = invocie[Year] - invoice[CurrentYear]

Then i wrote my measure as

LastMonthTotalValue =

SUMX(

FILTER('Invoice',Invoice[MonthOffset] = -1),

Invoice[InvoiceValue]

)

This worked and gave me the correct data but it really feels clumbersome as i may want dates for another table and would rather use a central date table

Thanks

Paul