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!

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Your previous calculation was completely overriding any filters on the date table, so it wasn't able to show values for a given date. By adding KEEPFILTERS you can combine the selected dates filter with the filter for a given date which comes from the visual.

    • ValeriaBreve's avatar
      ValeriaBreve
      Post Partisan
      3 years ago

      Thanks! My eyes can't se the actual difference between this case and the one from my earlier case - why was this calculation working well in the other report. I will need to better look into it. Anyway, for the aggreation issue, it actually does show up also in the .pbix I sent, I discovered it starts at the moment that you apply the calculation item to the report - from the tabular editor - without even actually using it. You see the "sigma" for the number columns go away, and if you try to add a number column to a table, PowerBI does not aggregate it any longer (it does keep it aggregated if it was before). Measures still work correctly. I have submitted a ticket with MSFT.

       

      Thanks againf or all your help!