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 connected via the date column (I made sure it is a pure "date" in the transactional table - no date).

 

In the transactional table, I have a calculated column:

Total Amount (kg) = 

if ('Transactional Table'[CONFIRMED_AMOUNT]=0,'Transactional Table'[SCHEDULED_AMOUNT],'Transactional Table'[CONFIRMED_AMOUNT])

 

which works.

When I create a (visual) table with the Datetable[Date] and this column, I see correctly the dates as of June 2022 (first entry in the transaction table), and the related amount.

 

Then I built a measure using this calculated column: 

Total Consumption (kg) =
    CALCULATE (
        sum ('Transactional Table'[Total Amount (kg)]),
        DATESBETWEEN (
            DateTable[Date],
            [StartDate],
            [EndDate]
        )
)
where End Date and Start Date are calculated via parameters.
 

Can somebody explain to me why, when I add this into the (visual) table with the DateTable[Date] and the calculated column [Total Amount (kg)], suddenly the table contains dates as of Jan 1st 2022, whith data for both the calculated column [Total Amount (kg)] and measure [Total Consumption (kg)], where there are no data for these dates? I can't understand this behaviour.

 

Thanks a lot in advance for your help,

Kind regards

Valeria

  • 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 )
		)
	)

9 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    can you share a PBIX with any confidential info removed ?

  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    3 years ago

    Hi John,

    I have tried my best to replicate the file.

    So, I cannot replicate the issue I have with the calculated column in the original file - which I cannot share as it is confidential. It looks the same, format is number, no blanks, but PowerBI will not allow me to do any aggregation with it. When I put this calculated column in a table, instead of having the sum as per specified summarization, I get discreet numbers... I don't understand what is going on. If you have any hints about what I can check, it would be much appreciated!

     

    Calculated Column:

    Total Amount (kg) = 

    if ('Transactional Table'[CONFIRMED_AMOUNT]=0,'Transactional Table'[SCHEDULED_AMOUNT],'Transactional Table'[CONFIRMED_AMOUNT])
     
    ... no summarizing possible

     

    However, even with the mock file, I can't get what I want  which is actually what you suggested to me on another post (Solved: Re: Use What-If parameter for selecting a date int... - Microsoft Power BI Community). I wanted to be able to let the users filter for dates through the use of parameter slicers (Days Before Today/ Days After Today), as you taught me . I have tried to apply the calculation group, but it does not work... the measure calculates for every date, so I cannot filter out what's = 0. What am I doing wrong? 

    File is on https://we.tl/t-tjWRmXRCAU

     

     

    Thanks a lot!

    Kind regards

    Valeria

     

     

    • 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!