cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
phillipd94 Frequent Visitor
Frequent Visitor

MTD Returning YTD Value in Cell

Hi,

 

I am creating a report using an excel file. It is straightforward enough. I just need to calcuate MTD, YTD and the variances between both for range of different years.

 

I have used these calculations before using quick measures and they worked perfectly. However my MTD Calculations are returning YTD if there is no MTD Value. The Total Calculation at the end is correct but the cells are not. In the first column my calculation works but it is a column not a measure so it has limitations.

 

Totalimage.png

Has anyone seen this before or know why it is occuring?

 

This is the calculation for my Column

SalesMTD =
IF( AND
        (AND(
              ('Item Ledger Entries'[Posting Date].[MonthNo]=(MONTH(TODAY()))),
              ('Item Ledger Entries'[Posting Date].[Year] = YEAR(TODAY()))),
       ('Item Ledger Entries'[Posting Date].[Date] <= TODAY())) ,
'Item Ledger Entries'[Sales Amount (Actual)],
BLANK())
 
This is the calculation for my MTD Measure
Sales Amount (Actual) MTD =
IF(
    ISFILTERED('Item Ledger Entries'[Posting Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALMTD(
        SUM('Item Ledger Entries'[Sales Amount (Actual)]),
        'Item Ledger Entries'[Posting Date]  //(Have tried this with .[Date] at the end but that returns blanks)
    )
)

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 275 members 2,732 guests
Please welcome our newest community members: