Forum Discussion
Sum measure fixed on a single date
Hello!
I'm really stucked on this subject, hope someone could help (:
I want to get one chart exactly as the following one, which I got using calculated columns (then the slicers filters on the visual are not working pretty well).
I've one Table with the Raw Data named 'FRAS' and another one with an active relation named 'Calendar' (I simple Calendar Table)
The column on the chart is the Sum of 'MONEY' (no matter the date) on the FRAS table splited by 'CATEGORY'. The 'MONEY' vary thought time, then the dashed lines is the forecast of these changes based on 'DATES' of the FRAS table. I would like to have the column (sum of the Money) fixed on the Today's date. For the previous chart I've used a calculated column on the Calendar Table with the following formula for each color of the bars:
Sum IL1 Money =
IF( Calendar[Date] = TODAY();
CALCULATE( SUM(FRAS[Money]);
ALLEXCEPT(FRAS; FRAS[Object]; FRAS[REGION]);
FRAS[Status] <> "Closed";
FRAS[Category] = "IL1");
0)
But, as I used calculated columns on the Calendar Table, the slicers are not working pretty well.
Then I'm trying to use some Measures instead. At least the slicers are working pretty well.
Althought, now the bar is being showed on every date of the chart.
The formula I'm using for the measure is:
Either enable the slicers on the calculated colum or to have the measure fixated on Today's date would solve my problem.
Thanks in advance!
Gabriel
Hi,
Please change original measure to this:
Sum Current IL5 Saving =
IF (
TODAY () >= MIN('Calendar_A110'[Date]) && TODAY() <= MAX('Calendar_A110'[Date]),
CALCULATE (
SUM ( FRAS[Saving Adj] ),
ALLEXCEPT (
FRAS,
FRAS[Object],
FRAS[REGION],
FRAS[Cell],
FRAS[Cell Label],
FRAS[PCR_Category],
FRAS[labels],
FRAS[PROJECT],
FRAS[Responsible Group Merged],
FRAS[status],
FRAS[IL-Level (Adjusted)],
FRAS[Concluded Milestone],
FRAS[Plan],
FRAS[Waiting List]
),
FRAS[PCR Status] <> "Dropped",
FRAS[IL-Level (Adjusted)] = "IL5"
) / 1000,
BLANK ()
)
And the result does not display due to your page filter and today’s data does not meet this filter.
In order to show the result, I temporarily remove this page filter to test.
I have kept the date hierarchy and now the result shows:
Best Regards,
Giotto Zhi
8 Replies
- v-gizhi-msft
Community Support
Hi,
According to your description, I choose one measure as an example and the others are the same.
Please take following steps:
1)Change original measures:
Sum Current IL5 Saving =
IF (
TODAY () IN FILTERS ( Calendar_A110[Date] ),
CALCULATE (
SUM ( FRAS[Saving Adj] ),
ALLEXCEPT (
FRAS,
FRAS[Object],
FRAS[REGION],
FRAS[Cell],
FRAS[Cell Label],
FRAS[PCR_Category],
FRAS[labels],
FRAS[PROJECT],
FRAS[Responsible Group Merged],
FRAS[status],
FRAS[IL-Level (Adjusted)],
FRAS[Concluded Milestone],
FRAS[Plan],
FRAS[Waiting List]
),
FRAS[PCR Status] <> "Dropped",
FRAS[IL-Level (Adjusted)] = "IL5"
) / 1000,
BLANK ()
)
2)Convert axis column to Date type:
3)The result shows only today’s data:
Best Regards,
Giotto Zhi
Hello v-gizhi-msft
Thanks for the help. It works!But I really wanted/needed the Date Hierarchy feature 😕
Is it possible to keep it?
Thanks!- v-gizhi-msft
Hi,
Please change original measure to this:
Sum Current IL5 Saving =
IF (
TODAY () >= MIN('Calendar_A110'[Date]) && TODAY() <= MAX('Calendar_A110'[Date]),
CALCULATE (
SUM ( FRAS[Saving Adj] ),
ALLEXCEPT (
FRAS,
FRAS[Object],
FRAS[REGION],
FRAS[Cell],
FRAS[Cell Label],
FRAS[PCR_Category],
FRAS[labels],
FRAS[PROJECT],
FRAS[Responsible Group Merged],
FRAS[status],
FRAS[IL-Level (Adjusted)],
FRAS[Concluded Milestone],
FRAS[Plan],
FRAS[Waiting List]
),
FRAS[PCR Status] <> "Dropped",
FRAS[IL-Level (Adjusted)] = "IL5"
) / 1000,
BLANK ()
)
And the result does not display due to your page filter and today’s data does not meet this filter.
In order to show the result, I temporarily remove this page filter to test.
I have kept the date hierarchy and now the result shows:
Best Regards,
Giotto Zhi
- amitchandak
Super User
Move your filter inside the calculation and Try
CALCULATE( SUM(FRAS[Money]); ALLEXCEPT(FRAS; FRAS[Object]; FRAS[REGION]); FRAS[Status] <> "Closed"; FRAS[Category] = "IL1", filter(Calendar,Calendar[Date] = TODAY();)Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Hello, amitchandak
Thanks for the feedback!I have tried the formula you sent and, unfortunatelly, it not worked 😕
No data is shown on the chart.- amitchandak
To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
