Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi Community. Please help
I want to have Columns from March - Feb. Each Month Must Display the Actual Sales for that Month (NOT TOTAL YTD), then the total column must give YTD. If the user selects April in the month Slicer. The April column will Have April's Data (NOT CUMULATIVE OR YTD) and in the Total Column the YTD from Mar .
Eg. 1
Mar Apr May YTD
10 20 30 60
Eg. 2If I select April from the Month Slicer
Mar Apr YTD
10 20 30
See OutCome Hoped.
Solved! Go to Solution.
Hi @SammiP ,
First create a dim date table as below:
Date = VALUES('Table'[Date])
Then create a measure as below:
Measure =
IF (
ISFILTERED ( 'Date'[Date] ),
IF (
ISINSCOPE ( 'Table'[Date] ),
IF (
MAX ( 'Table'[Date] ) <= SELECTEDVALUE ( 'Date'[Date] ),
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) )
),
BLANK ()
),
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] <= SELECTEDVALUE ( 'Date'[Date] ) )
)
),
SUM ( 'Table'[Value] )
)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @SammiP ,
First create a dim date table as below:
Date = VALUES('Table'[Date])
Then create a measure as below:
Measure =
IF (
ISFILTERED ( 'Date'[Date] ),
IF (
ISINSCOPE ( 'Table'[Date] ),
IF (
MAX ( 'Table'[Date] ) <= SELECTEDVALUE ( 'Date'[Date] ),
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] = MAX ( 'Table'[Date] ) )
),
BLANK ()
),
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] <= SELECTEDVALUE ( 'Date'[Date] ) )
)
),
SUM ( 'Table'[Value] )
)
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
have you created a file yet? If you import your table in and filter by that column it should do that automatically. Where are you struggling exactly?
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
74 | |
63 | |
51 | |
47 |
User | Count |
---|---|
211 | |
85 | |
64 | |
59 | |
56 |