Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago
Solved

Issue with measure

Hello, I have a PowerBI report which is quite simple: 1 transactional table + a date table. The transactional table has data since June 2022 - the date table has dates since 1/1/2022. The 2 are con...
  • johnt75's avatar
    johnt75
    3 years ago

    I don't know what is going on with the calculated column, but you can get the daily figures by amending your calculation item to be

    VAR numDaysBefore =
    SELECTEDVALUE ( 'Days Before Today'[Days Before Today] )
VAR numDaysAfter =
    SELECTEDVALUE ( 'Days After Today'[Days After Today] )
VAR baseDate =
    TODAY ()
VAR startDate = baseDate - numDaysBefore
VAR endDate = baseDate + numDaysAfter
VAR datesToUse = DATESBETWEEN (
            DateTable[Date],
            startDate,
            endDate
        )
VAR selectedDate = SELECTEDVALUE( DateTable[Date])
RETURN
	IF(
		selectedDate IN datesToUse,
		CALCULATE(
			SELECTEDMEASURE(),
			KEEPFILTERS( datesToUse )
		)
	)
johnt75's avatar
johnt75
Super User
3 years ago

I don't know what is going on with the calculated column, but you can get the daily figures by amending your calculation item to be

VAR numDaysBefore =
    SELECTEDVALUE ( 'Days Before Today'[Days Before Today] )
VAR numDaysAfter =
    SELECTEDVALUE ( 'Days After Today'[Days After Today] )
VAR baseDate =
    TODAY ()
VAR startDate = baseDate - numDaysBefore
VAR endDate = baseDate + numDaysAfter
VAR datesToUse = DATESBETWEEN (
            DateTable[Date],
            startDate,
            endDate
        )
VAR selectedDate = SELECTEDVALUE( DateTable[Date])
RETURN
	IF(
		selectedDate IN datesToUse,
		CALCULATE(
			SELECTEDMEASURE(),
			KEEPFILTERS( datesToUse )
		)
	)
ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago

Thanks! This works! Can you please explain to me the why the previous calculation was not working? For me to understand so that I can apply them correctly in the future. Thanks! 🙂

For the other issue, I raised a ticket with MSFT as I have no idea what is going on. I will share the explanation if interesting for the community. Thanks again!